if object_id('[temp]') is not null drop table [temp]
go
create table [temp]([groupID] int,[foldName] varchar(10),[parentID] int)
insert [temp] select 1,'文件1',0
union all select 2,'文件2',0
union all select 3,'文件1-1',1
union all select 4,'文件2-1',2
union all select 5,'文件1-1-1',3
union all select 6,'文件2-1-1',4
--SQL查询如下:
;WITH t AS
(
SELECT groupID,foldName,parentID,
path=CAST(foldName AS VARCHAR(8000))
FROM Temp AS A
WHERE NOT EXISTS
(
SELECT *
FROM Temp
WHERE groupID=A.parentID
)
UNION ALL
SELECT
A.*,
B.path+'/'+A.foldName
FROM Temp AS A
JOIN t AS B
ON A.parentID=B.groupID
)
SELECT groupID,path AS foldname FROM t order by groupID
/*
groupID path
------- ---------------------------
1 文件1
2 文件2
3 文件1/文件1-1
4 文件2/文件2-1
5 文件1/文件1-1/文件1-1-1
6 文件2/文件2-1/文件2-1-1
(6 行受影响)
*/
[sql server] 问题总结15--BOOM节点问题,树形数据,求出某个节点下的所有子节点
最新推荐文章于 2023-12-26 15:31:26 发布