34,575
社区成员
发帖
与我相关
我的任务
分享
有一表如下
select 1 id,0 pid union
select 2 id,0 pid union
select 3 id,1 pid union
select 4 id,1 pid union
select 5 id,2 pid union
select 6 id,2 pid union
select 7 id,3 pid
想得到如下的查询结果,
id pid mb
1 0 0_1
2 0 0_2
3 1 0_1_3
4 1 0_1_4
5 2 0_2_5
6 2 0_2_6
7 3 0_1_3_7
其中pid上上级目录的id
谢谢
;WITH CTET1
AS
(
select 1 id,0 pid union
select 2 id,0 pid union
select 3 id,1 pid union
select 4 id,1 pid union
select 5 id,2 pid union
select 6 id,2 pid union
select 7 id,3 pid
),CTET2
AS
(
SELECT *,CAST(RTRIM(pid)+'_'+RTRIM(id) AS VARCHAR(100)) AS Path1 FROM CTET1 WHERE CTET1.pid=0
UNION ALL
SELECT b.*,CAST(a.Path1+'_'+RTRIM(b.id) AS VARCHAR(100)) FROM CTET2 AS a INNER JOIN CTET1 AS b ON b.pid=a.id
)
SELECT * FROM CTET2 ORDER BY id
/*
id pid Path1
1 0 0_1
2 0 0_2
3 1 0_1_3
4 1 0_1_4
5 2 0_2_5
6 2 0_2_6
7 3 0_1_3_7
*/