是否可以在 SQL 中创建“树解析器”?
我有一张桌子:
ID Name Parent
1 a
2 b 1
3 c 1
4 d 3
现在我想要一个返回的 SQL 查询:
ID PATH
1 /a
2 /a/b
3 /a/c
4 /a/c/d
这可以用 SQL 实现吗?这会让我的很多事情变得更容易。任何帮助将不胜感激!
在 sql server 2005 及更高版本中使用 CTE,这是我必须执行此操作的代码片段:
WITH Paths([Level], [FullPath], [ID]) AS
(
SELECT
0 AS [Level],
Name AS FullPath,
ID
FROM dbo.Entity
WHERE (ParentEntityID IS NULL)
UNION ALL
SELECT
p.[Level] + 1 AS [Level],
CASE RIGHT(p.[FullPath], 1)
WHEN '\' THEN p.[FullPath] + c.[Name]
ELSE p.[FullPath] + '\' + c.[Name]
END AS FullPath,
c.ID
FROM dbo.Entity AS c
INNER JOIN Paths AS p ON p.ID = c.ParentEntityID
)
SELECT [FullPath], [ID]
FROM Paths
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)