日期:2014-05-18 浏览次数:20423 次
DECLARE @tab TABLE (Id INT, Parent_id INT) INSERT INTO @tab SELECT 1, NULL UNION ALL SELECT 2, 1 UNION ALL SELECT 3, 1 UNION ALL SELECT 4, 2 UNION ALL SELECT 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 行受影响) */