22,300
社区成员




select [一级地名], [二级地名],t3.name [三级地名] from (
select t1.id [t1_id],t1.name [一级地名], t0.id [t0_id],t0.name [二级地名] from (select id,name from city where parentid = 0)t1
left join city t0
on t0.parentid = t1.id
where t0.parentid !=0
) t2 left join city t3
on t2.t0_id = t3.parentid
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(id INT PRIMARY KEY,[name] NVARCHAR(10),parentId INT)
INSERT INTO t
SELECT 1,'北京市',0
UNION ALL SELECT 2,'山东省',0
UNION ALL SELECT 3,'昌平区',1
UNION ALL SELECT 4,'海淀区',1
UNION ALL SELECT 5,'沙闸镇',3
UNION ALL SELECT 6,'马池口镇',3
UNION ALL SELECT 7,'中关村',4
UNION ALL SELECT 8,'上地',4
UNION ALL SELECT 9,'烟台市',2
UNION ALL SELECT 10,'青岛市',2
UNION ALL SELECT 11,'牟平区',9
UNION ALL SELECT 12,'芝豪区',9
UNION ALL SELECT 13,'即墨区',10
UNION ALL SELECT 14,'城阳',10
;WITH cte AS (
SELECT id,NAME,parentId,1 AS level FROM t WHERE parentId=0
UNION ALL
SELECT t.id,t.NAME,t.parentId,cte.level+1 AS level FROM t
INNER JOIN cte ON t.parentId=cte.id
)
SELECT
t1.name AS [一级地名]
,t2.name AS [二级地名]
,t3.name AS [三级地名]
FROM
(SELECT * FROM cte WHERE LEVEL=1) AS t1
INNER JOIN
(SELECT * FROM cte WHERE LEVEL=2) AS t2 ON t1.id=t2.parentId
INNER JOIN
(SELECT * FROM cte WHERE LEVEL=3) AS t3 ON t2.id=t3.parentId
ORDER BY 1,2,3