SQL语句查询父级ID

DECLARE @Table TABLE ( Id INT, Name NVARCHAR(100), Pid INT ,SortOrder int)
INSERT @Table
SELECT 1,N'上海',NULL,1
UNION SELECT 2,N'江苏',NULL,1
UNION SELECT 11,N'黄埔区',1,2
UNION SELECT 12,N'徐汇区',1,2
UNION SELECT 21,N'苏州市',2,2
UNION SELECT 22,N'昆山市',2,2
UNION SELECT 111,N'南京东路',11,3
UNION SELECT 211,N'南京西路',11,3

DECLARE @Id INT
SET @Id=211
;WITH cte AS (
        SELECT * FROM @Table WHERE id = @Id
        UNION ALL SELECT t.* FROM @Table t JOIN cte ON t.id = cte.pid
             )
SELECT STUFF((SELECT ','+CONVERT(VARCHAR(10),ID) FROM cte ORDER BY SortOrder FOR XML PATH('')),1,1,''),STUFF((SELECT ','+name FROM cte ORDER by SortOrder FOR XML PATH('') ),1,1,'')

转载于:https://www.cnblogs.com/guowhua/p/5954953.html