有如下表 A：
Id Parent_id
1 null
2 1
3 1
4 2
5 3
请写出一个算法得到如下结果（顺序要一致）
Id Path
1 /
2 /2/
4 /2/4/
3 /3/
5 /3/5/
------解决方案--------------------
CTE递归 有请楼下。
------解决方案--------------------
- SQL code
DECLARE @tab TABLE (Id INT, Parent_id INT)INSERT INTO @tabSELECT 1, NULL UNION ALL SELECT 2, 1 UNION ALLSELECT 3, 1 UNION ALLSELECT 4, 2 UNION ALLSELECT 5, 3;with cte AS ( SELECT Id,[PATH]=CAST('/' AS VARCHAR),Parent_id FROM @tab WHERE Parent_id IS NULL UNION ALL SELECT a.Id,[PATH]=CAST(c.[PATH]+CAST(a.Id AS VARCHAR)+'/' AS VARCHAR), a.Parent_id FROM @tab a JOIN cte c ON a.Parent_id=c.Id)SELECT id,PATH FROM cte ORDER BY id --test:/*id PATH----------- ------------------------------1 /2 /2/3 /3/4 /2/4/5 /3/5/(5 行受影响)*/