数据示例：
DROP TABLE #
SELECT *
INTO # FROM (
SELECT 'A' A,'B' B UNION
SELECT 'B','C' UNION
SELECT 'C','D' UNION
SELECT 'E','F' UNION
SELECT 'F','G'
)Z
SELECT *
FROM #
我的要求就是想要的结果是：谢谢！！！在线等待.
SELECT 'A' A,'B' B,0 AS [level],'A' AS ParentName UNION
SELECT 'B','C',1,'A' UNION
SELECT 'C','D',2,'A' UNION
SELECT 'E','F',0,'E' UNION
SELECT 'F','G',1,'E'
------解决思路----------------------
SELECT *
INTO # FROM (
SELECT 'A' A,'B' B UNION
SELECT 'B','C' UNION
SELECT 'C','D' UNION
SELECT 'E','F' UNION
SELECT 'F','G'
)Z
;WITH Cte
AS(
SELECT *,[Level]=0 FROM # AS a WHERE NOT EXISTS(SELECT 1 FROM # WHERE B=a.A)
UNION ALL
SELECT a.*,[Level]=b.[Level]+1 FROM # AS a INNER JOIN Cte AS b ON b.B=a.A
)
SELECT * FROM Cte ORDER BY 1
/*
A B 0
B C 1
C D 2
E F 0
F G 1*/
------解决思路----------------------
SQL2005有效
;WITH CTE AS(
SELECT T1.*,0[LEVEL],T1.A [ParentName] FROM # T1 LEFT JOIN # T2 ON T1.A=T2.B WHERE T2.A IS NULL
UNION ALL
SELECT T1.*,T2.[LEVEL]+1,T2.[ParentName] FROM # T1 JOIN CTE T2 ON T1.A=T2.B
)
SELECT * FROM CTE ORDER BY A