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

zzh133 2018-03-09 06:39:01
...全文
3040 18 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
utempdclhn2 2019-09-13
  • 打赏
  • 举报
回复
--方法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
Yanhuogege 2019-05-08
  • 打赏
  • 举报
回复
引用 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
Yanhuogege 2019-05-08
  • 打赏
  • 举报
回复
引用 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 2019-05-08
  • 打赏
  • 举报
回复
引用 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级都存在。
Serena945945 2019-04-22
  • 打赏
  • 举报
回复
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 加油!
qq_43203274 2019-04-06
  • 打赏
  • 举报
回复
rongo360 2018-07-31
  • 打赏
  • 举报
回复
这个样子滴:

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

RunEric 2018-07-29
  • 打赏
  • 举报
回复
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
倾听自然123 2018-07-25
  • 打赏
  • 举报
回复
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
要亲亲QWQ 2018-07-22
  • 打赏
  • 举报
回复
SingleLine_wuht 2018-07-11
  • 打赏
  • 举报
回复
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


cenlm 2018-06-21
  • 打赏
  • 举报
回复


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



这样吧,不知道对不对。
吉普赛的歌 2018-03-09
  • 打赏
  • 举报
回复
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



这个题的难度高了一点, 不适合作为一般性的面试题
zzh133 2018-03-09
  • 打赏
  • 举报
回复
引用 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很强一开始我也想用递归来着,但对递归又不熟就没整出来
zzh133 2018-03-09
  • 打赏
  • 举报
回复
引用 2 楼 sinat_28984567 的回复:
按照级别先读取三个级别的数据,做成三个临时表,然后利用parentid关联查询
可以的66
zzh133 2018-03-09
  • 打赏
  • 举报
回复
最后代码是这样的: 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
二月十六 2018-03-09
  • 打赏
  • 举报
回复
按照级别先读取三个级别的数据,做成三个临时表,然后利用parentid关联查询
zzh133 2018-03-09
  • 打赏
  • 举报
回复
来人啊来人啊

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧