22,300
社区成员




--DROP TABLE #TData
;WITH TData(father,son)
AS
(
SELECT 'A','B'
UNION ALL SELECT 'A','C'
UNION ALL SELECT 'B','C'
UNION ALL SELECT 'B','D'
UNION ALL SELECT 'A','F'
UNION ALL SELECT 'C','D'
UNION ALL SELECT 'D','F'
UNION ALL SELECT 'E','F'
)
SELECT
*
INTO #TData
FROM TData
--
DECLARE @facher VARCHAR(1)='A',@lev INT=1
DECLARE @TData TABLE(data VARCHAR(1),level INT,Ord VARCHAR(20));
INSERT INTO @TData
( data, level,Ord )
SELECT son,@lev,RIGHT(1000+ROW_NUMBER()OVER(ORDER BY @facher),3) FROM #TData WHERE father=@facher
WHILE @@ROWCOUNT>0
BEGIN
SET @lev+=1
INSERT INTO @TData
( data, level,Ord )
SELECT a.son, @lev,b.Ord+RIGHT(1000+ROW_NUMBER()OVER(ORDER BY @facher),3) FROM #TData AS a INNER JOIN @TData AS b ON b.data=a.father AND b.level=@lev-1 --WHERE NOT EXISTS(SELECT 1 FROM @TData WHERE data=a.son)
END
SELECT * FROM @TData ORDER BY Ord
/*
data level Ord
B 1 001
C 2 001001
D 3 001001001
F 4 001001001001
D 2 001002
F 3 001002002
C 1 002
D 2 002003
F 3 002003003
F 1 003
*/