递归CTE可以用于具有累积滴入子连接的分层行吗?

Question

我对"组"使用递归CTE;

第一个表"Groups"(Project_Estimate_Group)是有ID和父级的。第二个表" groupoption& quot;(Project_Estimate_Group_Option)容纳了组和选项之间的多对多关系。如果你将一个选项添加到任何级别，它就会渗透到该分支中的任何一个子级别。

我需要将Groups的父级和递归子级连接到GroupOptions，并让这些连接"滴流"。到每个递归级别，以便在每个级别上都有到当前选项的连接以及到分支中每个父级的选项的连接。

我需要为每个没有OptionID的组创建一行，为每个加入OptionID的组创建一行。有些关卡没有选项。

这可以用递归CTE完成吗?

Table1"Groups">

Parent   ID
-------- ------
NULL     1
1        2
2        3
NULL     4
4        5

表"GroupOptions">

GroupID  OptionID
-------- --------
1        10
1        11
2        12
5        20

加入后我需要

Parent   ID       OptionID
-------- -------- ----------
NULL     1        0
NULL     1        10
NULL     1        11
1        2        0
1        2        10
1        2        11
1        2        12
2        3        0
2        3        10
2        3        11
2        3        12
NULL     4        0
4        5        0
4        5        20

下面的查询会比较接近，但只返回当前Group及其父组的Options，而不返回其祖父组或以上

DECLARE @EstimateID int = (SELECT MAX(ID) FROM dbo.Project_Estimate);
WITH egroup (EstimateID, ParentID, GroupID, OptionID) AS
(
SELECT @EstimateID, NULL, peg.ID, 0
FROM dbo.Project_Estimate_Group peg
WHERE peg.Estimate = @EstimateID AND peg.Parent IS NULL
UNION ALL
SELECT @EstimateID, NULL, peg.ID, pego.OptionID
FROM dbo.Project_Estimate_Group peg
JOIN dbo.Project_Estimate_Group_Option pego on peg.ID = pego.GroupID
WHERE peg.Estimate = @EstimateID AND peg.Parent IS NULL
UNION ALL
SELECT @EstimateID, peg.Parent, peg.ID, 0
FROM dbo.Project_Estimate_Group peg
JOIN egroup on peg.Parent = egroup.GroupID
UNION ALL
SELECT @EstimateID, peg.Parent, peg.ID, pego.OptionID
FROM dbo.Project_Estimate_Group peg
JOIN egroup on peg.Parent = egroup.GroupID
JOIN dbo.Project_Estimate_Group_Option pego on peg.ID = pego.GroupID
UNION ALL
SELECT @EstimateID, peg.Parent, peg.ID, pego.OptionID
FROM dbo.Project_Estimate_Group peg
JOIN egroup on peg.Parent = egroup.GroupID
JOIN dbo.Project_Estimate_Group_Option pego on peg.Parent = pego.GroupID
)
SELECT DISTINCT EstimateID, ParentID, GroupID, OptionID 
FROM egroup
JOIN dbo.Project_Estimate e on egroup.EstimateID = e.ID

Answer 1

试试这个:

;WITH CTE AS 
(   
-- Get all Groups - GroupOptions combinations
SELECT g.Parent, g.ID, go.OptionID
FROM Groups g
INNER JOIN GroupOptions go ON g.ID = go.GroupID  
UNION ALL
-- Go down one level passing option value
SELECT g.Parent, g.ID, c.OptionID
FROM CTE c
INNER JOIN Groups g ON c.ID = g.Parent
)
SELECT *
FROM CTE
UNION ALL
-- Union each group row without the OptionID
SELECT Parent, ID, 0
FROM Groups
ORDER BY ID, OptionID

演示