sql的一个面试题，难住了，大神们来看看

[zzh133]

[zzh133]

来人啊来人啊

[二月十六]

按照级别先读取三个级别的数据，做成三个临时表，然后利用parentid关联查询

[zzh133]

最后代码是这样的： select * into #a1 from DuoJi where ParentId=0 select * into #a2 from DuoJi where ParentId in (select id from #a1) select * into #a3 from DuoJi where ParentId in (select id from #a2) select a1.Name as '一级地名',a2.Name as '二级地名',a3.Name as '三级地名' from #a3 a3 inner join #a2 a2 on a3.ParentId=a2.Id inner join #a1 a1 on a2.ParentId=a1.Id

[zzh133]

引用 2 楼 sinat_28984567 的回复:

按照级别先读取三个级别的数据，做成三个临时表，然后利用parentid关联查询

可以的66

[zzh133]

引用 5 楼 yenange 的回复:

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

这个题的难度高了一点， 不适合作为一般性的面试题

666很强一开始我也想用递归来着，但对递归又不熟就没整出来

[吉普赛的歌]

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

这个题的难度高了一点， 不适合作为一般性的面试题

[cenlm]

select faddr.name '一级地名',saddr.name '二级地名',taddr.name '三级地名' from address a
join (SELECT id,name,parent_id from address) faddr on a.id=faddr.id
join (SELECT id,name,parent_id from address) saddr on faddr.id=saddr.parent_id
join (SELECT id,name,parent_id from address) taddr on saddr.id=taddr.parent_id



这样吧，不知道对不对。

[SingleLine_wuht]

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

[要亲亲QWQ]

[倾听自然123]

select a.name as 一级地名,b.name as 二级地名,c.name 三级地名 from address a,address b,address c where a.id=b.farendid and b.id=c.farendid

[RunEric]

select a.name '一级地名' ,b.name '二级地名',c.name '三级地名' from t a
inner JOIN (select name,parentId,id from t) b on a.id=b.parentId
inner join (select name,parentId from t) c on b.id=c.parentId
where a.parentid=0

[rongo360]

这个样子滴：

SELECT a.name AS "一级" , b.name AS "二级" ,c.name AS "三级" FROM t a ,t b, t c WHERE a.`id`=b.`pid` AND b.`id` =c.`pid` AND a.`pid`=0

[qq_43203274]

[Serena945945]

select a.Name as '一级地名',b.Name as '二级地名',c.Name as '三级地名' from Address a inner join Address b on b.ParentId = a.Id inner join Address c on c.ParentId = b.Id where a.ParentId = 0 加油！

[Yanhuogege]

引用 2 楼 二月十六 的回复:

按照级别先读取三个级别的数据，做成三个临时表，然后利用parentid关联查询

引用 4 楼 zzh133 的回复:

最后代码是这样的：
select * into #a1 from DuoJi where ParentId=0
select * into #a2 from DuoJi where ParentId in (select id from #a1)
select * into #a3 from DuoJi where ParentId in (select id from #a2)

select a1.Name as '一级地名',a2.Name as '二级地名',a3.Name as '三级地名' from #a3 a3 inner join #a2 a2 on a3.ParentId=a2.Id inner join #a1 a1 on a2.ParentId=a1.Id

你这种写法是有问题的，当没有下一级地名时候数据出不来的。除非是1，2，3级都存在。

[Yanhuogege]

引用 7 楼 qq_28580453 的回复:

select faddr.name '一级地名',saddr.name '二级地名',taddr.name '三级地名' from address a
join (SELECT id,name,parent_id from address) faddr on a.id=faddr.id
join (SELECT id,name,parent_id from address) saddr on faddr.id=saddr.parent_id
join (SELECT id,name,parent_id from address) taddr on saddr.id=taddr.parent_id



这样吧，不知道对不对。

你这种写法只有在定义的三级都存在时候就没问题，如果定义到第四级城市，且只有1个三级城市有第四级城市时候 数据就只能查询出有第四级城市那笔数据。如果用left ，那没有第四级城市的时候 数据就乱了。

[Yanhuogege]

引用 7 楼 qq_28580453 的回复:

select faddr.name '一级地名',saddr.name '二级地名',taddr.name '三级地名' from address a
join (SELECT id,name,parent_id from address) faddr on a.id=faddr.id
join (SELECT id,name,parent_id from address) saddr on faddr.id=saddr.parent_id
join (SELECT id,name,parent_id from address) taddr on saddr.id=taddr.parent_id



这样吧，不知道对不对。

稍作改动，第一级加where条件
select faddr.name '一级地名',saddr.name '二级地名',taddr.name '三级地名'
from address a join (SELECT id,name,parentid from address where parentid=0) faddr on a.id=faddr.id
left join (SELECT id,name,parentid from address) saddr on faddr.id=saddr.parentid
left join (SELECT id,name,parentid from address) taddr on saddr.id=taddr.parentid

[utempdclhn2]

--方法2
;with tt as (
select 1 as [level],* from tb where parentId=0
union all
select tt.level+1,a.id,a.name,a.parentId from tb a inner join tt on a.parentId=tt.id
)
select t1.name as'一级地名',t2.name as'二级地名',t3.name as'三级地名' from(
select * from tt where level=3)t3 left join (select * from tt where level=2) t2 on t3.parentId=t2.id
left join (select * from tt where level=1) t1 on t2.parentId=t1.id order by 1,2,3
--方法2
select a.name as 一级地名,b.name as 二级地名,c.name as 三级地名 from(
(select * from tb where parentid=0) a left join
(select * from tb ) b on b.parentid =a.id left join
(select * from tb) c on c.parentid=b.id
)order by 1,2,3