22,206
社区成员
发帖
与我相关
我的任务
分享
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)
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
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)