17,377
社区成员
发帖
与我相关
我的任务
分享
DECLARE @level int
SET @level = 1
SELECT *, @level level
INTO #r
FROM table1
WHERE val='root' OR pid='/'
WHILE (@@ROWCOUNT <> 0)
BEGIN
SET @level = @level+1
INSERT INTO #r
SELECT t.id,t.val,
CASE WHEN r.val='root' THEN t.pid ELSE r.pid END,
@level
FROM #r r
JOIN table1 t
ON r.id = t.pid
WHERE r.level = @level-1
END
SELECT * FROM #r ORDER BY id
--SQL Server
WITH r AS (
SELECT * FROM table1 WHERE val='root' OR pid='/'
UNION ALL
SELECT t.id,t.val,
CASE WHEN r.val='root' THEN t.pid ELSE r.pid END
FROM r
JOIN table1 t
ON r.id = t.pid
)
SELECT * FROM r ORDER BY id
--SQL Server
WITH table1(id,val,pid) AS (
SELECT '1','aa','/' UNION ALL
SELECT '2','root','/' UNION ALL
SELECT '3','cc','1' UNION ALL
SELECT '4','dd','1' UNION ALL
SELECT '5','ee','3' UNION ALL
SELECT '6','ff','2' UNION ALL
SELECT '7','gg','6'
)
,r AS (
SELECT * FROM table1 WHERE val='root' OR pid='/'
UNION ALL
SELECT t.id,t.val,
CASE WHEN r.val='root' THEN t.pid ELSE r.pid END
FROM r
JOIN table1 t
ON r.id = t.pid
)
SELECT * FROM r ORDER BY id
id val pid
---- ---- ----
1 aa /
2 root /
3 cc /
4 dd /
5 ee /
6 ff 2
7 gg 2