我正在构建一个查询,该查询为数据库中的每个根输出所有权层次结构。我正在成功地使用递归CTE,因为我目前可以实现以下数据输出:
rootID RootName RelatedName
1 ABA GPS
1 ABA PIG
1 ABA BBY
1 ABA PIG
2 PIG DDS
2 PIG GPS
我试图实现的是一个组ID列,其中的数据可能如下所示:
GroupID rootID RootName RelatedName
100 1 ABA GPS
100 1 ABA PIG
100 1 ABA BBY
100 1 ABA PIG
100 2 PIG DDS
100 2 PIG GPS
同样对于组200、300、,。。。等等。递归CTE的哪一部分代码可以被注入以实现上述结果?
;WITH cte_Rel AS (
SELECT
<columns>
FROM #RawRel r
WHERE 1 = 1
AND <initial Conditions>
UNION ALL
SELECT
<Columns>
FROM #RawRel r
JOIN cte_Rel c ON r.RootName = c.RelatedName
)
SELECT DISTINCT * FROM cte_Rel
OPTION (MAXRECURSION 100)
您可以在回避CTE的锚定部分添加一个行号。乘以100,在CTE的第二部分中重复相同的列。
如果您喜欢交互式代码,请使用Fiddle。
样本数据
如果没有实际的查询和样本输入数据,就很难完美地复制当前的输出,所以我生成了自己的样本数据。
create table RelData
(
ParentId int,
Id int,
Name nvarchar(3)
);
insert into RelData (ParentId, Id, Name) values
(null, 1, 'A00'), -- tree A
(1, 2, 'A10'),
(2, 3, 'A11'),
(2, 4, 'A12'),
(1, 5, 'A20'),
(5, 6, 'A21'),
(null, 7, 'B00'), -- tree B
(7, 8, 'B10'),
(8, 9, 'B11');
解决方案
WITH cte_Rel AS (
SELECT row_number() over(order by rd.Id) * 100 as TreeId, -- number to roots and multiply the root number by 100
rd.Id, rd.Name, rd.ParentId, convert(nvarchar(3), null) as ParentName
FROM RelData rd
WHERE rd.ParentId is null
UNION ALL
SELECT c.TreeId, -- repeat the tree number
rd.Id, rd.Name, rd.ParentId, c.name
FROM RelData rd
JOIN cte_Rel c ON rd.ParentId = c.Id
)
SELECT c.TreeId, c.ParentId, c.ParentName, c.Name
FROM cte_Rel c
where c.ParentId is not null
order by c.ParentId;
结果
TreeId ParentId ParentName Name
------ -------- ---------- ----
100 1 A00 A10
100 1 A00 A20
100 2 A10 A11
100 2 A10 A12
100 5 A20 A21
200 7 B00 B10
200 8 B10 B11