请教:如何实现这样的省、市、区效果?

idragonet 2018-10-17 01:53:01
create table t
(id int,
code int,
name varchar(20),
parentcode int)
insert into t values (1,10000,'浙江',0)
insert into t values (2,20000,'安徽',0)
insert into t values (3,11000,'杭州',10000)
insert into t values (4,12000,'宁波',10000)
insert into t values (5,21000,'合肥',20000)
insert into t values (6,22000,'宣城',20000)
insert into t values (7,11100,'萧山',11000)
insert into t values (8,11300,'滨江',11000)
insert into t values (9,11300,'富阳',11000)



...全文
114 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
idragonet 2018-10-18
  • 打赏
  • 举报
回复
引用 2 楼 RINK_1 的回复:

select *
from
(select name as name_1,'' as name_2,''as name_3,1 as level from t where parentcode=0
union all
select A.name,B.name,'',2
from t A
join t B on A.code=B.parentcode and A.parentcode=0
union all
select A.name,B.name,C.name,3
from t A
join t B on A.code=B.parentcode and A.parentcode=0
join t C on B.code=C.parentcode) as A
order by name_1,name_2,level


谢谢这个就是要的效果。
RINK_1 2018-10-17
  • 打赏
  • 举报
回复

select *
from
(select name as name_1,'' as name_2,''as name_3,1 as level from t where parentcode=0
union all
select A.name,B.name,'',2 
from t A
join t B on A.code=B.parentcode and A.parentcode=0
union all
select A.name,B.name,C.name,3
from t A
join t B on A.code=B.parentcode and A.parentcode=0
join t C on B.code=C.parentcode) as A
order by name_1,name_2,level
mingqing6364 2018-10-17
  • 打赏
  • 举报
回复
WITH t1 as (
SELECT
*
FROM
t
WHERE
parentcode = 0
), t2 as (
SELECT
*
FROM
t
WHERE
RIGHT(parentcode, 4) = 0
), t3 as (
SELECT
*
FROM
t
WHERE
RIGHT(parentcode, 3) = 0
)

SELECT
t1.code code1,
t1.name name1,
null code2,
null name2,
null code3,
null name3
FROM
t1
UNION ALL
SELECT
t1.code code1,
t1.name name1,
t2.code code2,
t2.name name2,
null code3,
null name3
FROM
t1 INNER JOIN t2 ON t1.code = t2.parentcode
UNION ALL
SELECT
t1.code code1,
t1.name name1,
t2.code code2,
t2.name name2,
t3.code code3,
t3.name name3
FROM
t1 INNER JOIN t2 ON t1.code = t2.parentcode
INNER JOIN t3 ON t2.code = t3.parentcode
ORDER BY
code1, code2, code3


code1 name1 code2 name2 code3 name3
------- ------- ------- ------- ------- -------
10000 浙江 NULL NULL NULL NULL
10000 浙江 11000 杭州 NULL NULL
10000 浙江 11000 杭州 11100 萧山
10000 浙江 11000 杭州 11300 滨江
10000 浙江 11000 杭州 11300 富阳
10000 浙江 12000 宁波 NULL NULL
20000 安徽 NULL NULL NULL NULL
20000 安徽 21000 合肥 NULL NULL
20000 安徽 22000 宣城 NULL NULL
(9 rows affected)

22,206

社区成员

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

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