我有一个闭包表，其中可能有多个父级，相关的数据表如下所示：
CREATE TABLE [data] (
id int IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(100)
)
CREATE TABLE closure (
id int IDENTITY(1,1) PRIMARY KEY,
[src_id] int,
[dst_id] int,
[depth] int,
FOREIGN KEY ([src_id]) REFERENCES [data](id),
FOREIGN KEY ([dst_id]) REFERENCES [data](id)
)
现在我用DDL创建了一些测试数据，如下所示：
数据表
INSERT [data] ([name]) VALUES (N'data1')
INSERT [data] ([name]) VALUES (N'data2')
INSERT [data] ([name]) VALUES (N'data3')
INSERT [data] ([name]) VALUES (N'data4')
INSERT [data] ([name]) VALUES (N'data5')
INSERT [data] ([name]) VALUES (N'data6')
结束表
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (1, 1, 0)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (2, 2, 0)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (3, 3, 0)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (4, 4, 0)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (5, 5, 0)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (6, 6, 0)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (1, 2, 1)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (2, 5, 1)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (1, 5, 2)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (5, 6, 1)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (2, 6, 2)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (1, 6, 3)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (1, 3, 1)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (3, 4, 1)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (1, 4, 2)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (4, 5, 1)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (3, 5, 2)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (1, 5, 3)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (4, 6, 2)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (3, 6, 3)
INSERT [closure] ([src_id], [dst_id], [depth]) VALUES (1, 6, 4)
现在我想要的是一个查询，它输出每个数据集的每个路径，直到该路径不再有src_id，我想用以下语句来查询：
;WITH hierarchy_cte AS (
SELECT [data].id, [data].name, CAST ([data].name AS VARCHAR(100)) AS path , 0 as pathDepth
FROM closure
INNER JOIN [data]
ON [data].id = closure.src_id
WHERE closure.depth = 0
UNION ALL
SELECT closure.src_id, hierarchy_cte.name, CAST(([srcdata].name + ' / ' + hierarchy_cte.path) AS VARCHAR(100)) , hierarchy_cte.pathDepth + 1
FROM closure
INNER JOIN [data] srcdata
ON srcdata.id = closure.src_id
INNER JOIN [data]
ON [data].id = closure.dst_id
inner join [hierarchy_cte]
ON [data].id = hierarchy_cte.id
AND closure.depth = 1
)
SELECT * FROM hierarchy_cte
结果如下：
+----+-------+---------------------------------------+-----------+
| id | name | path | pathDepth |
+----+-------+---------------------------------------+-----------+
| 1 | data1 | data1 | 0 |
| 2 | data2 | data2 | 0 |
| 3 | data3 | data3 | 0 |
| 4 | data4 | data4 | 0 |
| 5 | data5 | data5 | 0 |
| 6 | data6 | data6 | 0 |
| 5 | data6 | data5 / data6 | 1 |
| 2 | data6 | data2 / data5 / data6 | 2 |
| 4 | data6 | data4 / data5 / data6 | 2 |
| 3 | data6 | data3 / data4 / data5 / data6 | 3 |
| 1 | data6 | data1 / data3 / data4 / data5 / data6 | 4 |
| 1 | data6 | data1 / data2 / data5 / data6 | 3 |
| 2 | data5 | data2 / data5 | 1 |
| 4 | data5 | data4 / data5 | 1 |
| 3 | data5 | data3 / data4 / data5 | 2 |
| 1 | data5 | data1 / data3 / data4 / data5 | 3 |
| 1 | data5 | data1 / data2 / data5 | 2 |
| 3 | data4 | data3 / data4 | 1 |
| 1 | data4 | data1 / data3 / data4 | 2 |
| 1 | data3 | data1 / data3 | 1 |
| 1 | data2 | data1 / data2 | 1 |
+----+-------+---------------------------------------+-----------+
我认为我的陈述会沿着所有可能的路径进行，并在没有src_id时结束。我只是被卡住了。我想要的结果是：
+----+-------+---------------------------------------+-----------+
| id | name | path | pathDepth |
+----+-------+---------------------------------------+-----------+
| 1 | data1 | data1 | 0 |
| 2 | data2 | data1 / data2 | 1 |
| 3 | data3 | data1 / data3 | 1 |
| 4 | data4 | data1 / data3 / data4 | 2 |
| 5 | data5 | data1 / data3 / data4 / data5 | 3 |
| 5 | data5 | data1 / data2 / data5 | 2 |
| 6 | data6 | data1 / data3 / data4 / data5 / data6 | 4 |
| 6 | data6 | data1 / data2 / data5 / data6 | 3 |
+----+-------+---------------------------------------+-----------+
这个问题似乎没那么难，但我就是想不通。
您需要在目的地加入，我在CTE 中添加了closure.dst_id
然后，我从hierarchy_cte中选择所有内容，并将dst_id与src_id 连接起来
FROM hierarchy_cte
inner join closure
on closure.src_id=hierarchy_cte.dst_id
AND closure.depth = 1
最后，我们需要只选择根作为CTE的第一步。让我们将根定义为任何不是闭包关系((目的地的节点
select * from closure c1
where depth=0
and not exists(select 1 from closure c2 where c2.depth>=1 and c1.src_id=c2.dst_id)
或者
select * from closure c1
where depth=0
and not exists(select 1 from closure c2 where c2.src_id<>c2.dst_id and c1.src_id=c2.dst_id)
使用它，我们得到了最后一个查询：
;WITH hierarchy_cte AS (
SELECT [data].id, [data].name, CAST ([data].name AS VARCHAR(100)) AS path , closure.depth as pathDepth, closure.dst_id
FROM closure
INNER JOIN [data]
ON [data].id = closure.src_id
WHERE closure.depth = 0
and not exists(select 1 from closure c2 where c2.depth>=1 and closure.src_id=c2.dst_id)
UNION ALL
SELECT hierarchy_cte.id,data.name,CAST ( [hierarchy_cte].path+'/'+ [data].name AS VARCHAR(100)) AS path,pathDepth+1,closure.dst_id
from [hierarchy_cte]
inner join closure
on closure.src_id=hierarchy_cte.dst_id
and closure.depth=1
INNER JOIN [data] srcdata
ON srcdata.id = closure.src_id
INNER JOIN [data]
ON [data].id = closure.dst_id
)
SELECT id,name,path,pathDepth FROM hierarchy_cte
order by path