递归sql，sql server 2012

Question

我正在尝试解决一个递归SQL问题，本质上是聚合一组记录的状态。

就问题而言，有两个表。一个维护聚合/层次结构"GROUP_MEMBERS“，另一个包含单个项目" items”。

"GROUP_MEMBERS“看起来很相似(ID是GROUPID，CHILDTYPE为0代表一个组，1代表单个项目，ID是子项目ID (因此groupid代表类型0，itemid代表类型1) )

ID | CHILDTYPE | CHILDID

1       0        2
1       1        1
2       1        2
2       1        3
2       1        4

在这个例子中，我的"ITEMS“表只有两列：

ID | STATUS

1     0
2     1
3     0
4     0
5     0

实际上，我试图做的是递归地拉回一个组下的所有“项”，ID和状态(因为组可以包含其他组)。因此，对于我提供的示例数据，如果我向它传递GROUPID 1，它将返回条目1-4及其状态；GROUPID 2将返回条目2-4及其状态，依此类推。

我假设我需要通过一个函数来做这件事并返回一个表，但是我甚至不确定从哪里开始。

Answer 1

这是一个很好的拼图，我得告诉你:)希望它是你想要的。

DECLARE @GroupRootID INT = 1
DECLARE @GROUP_MEMBERS TABLE (ID int, CHILDTYPE int, CHILDID int)
DECLARE @ITEMS TABLE (ID int, STATUS int)

INSERT INTO @GROUP_MEMBERS VALUES
(1,0,2), (1,1,1), (2,1,2), (2,0,3), (2,0,4), (2,1,3), (2,1,4), (3,1,5), (4,1,5)

INSERT INTO @ITEMS VALUES
(1,0), (2,1), (3,0), (4,0), (5,1)

--              1
--            /   \
--          2     items         items: 1 => 1,1,1
--        / |   \
--      3   4   items           items: 2,3,4 => 2,1,2 - 2,1,3 - 2,1,4
--      |   \   
--    items items               items (3): 5 => 3,1,5
--                              items (4); 5 => 4,1,5

/* Recursivly build the GROUP tree (groups that have subgroups, CHILDTYPE=0), but NOT the lead nodes (CHILDTYPE = 1) */
;WITH GROUP_TREE
AS
(
    /* SELECT all parents */
    SELECT ParentGroups.*, 0 AS LEVEL
    FROM @GROUP_MEMBERS AS ParentGroups
    WHERE ParentGroups.CHILDTYPE = 0
    AND ParentGroups.ID = @GroupRootID

    UNION ALL

    /* SELECT all childs groups for the parents */
    SELECT ChildGroups.*, LEVEL + 1
    FROM @GROUP_MEMBERS AS ChildGroups
    INNER JOIN GROUP_TREE AS Parent ON Parent.CHILDID = ChildGroups.ID
    WHERE ChildGroups.CHILDTYPE = 0
)

/* We now have all groups with their subgroups (not leaf nodes) */
/* Then join the leaf nodes (groups that are no subtree) */
/* Finally union the items from the root node and join the ITEMS to the leaf nodes to get the status */
/* Mind you though that ITEM 5 is linked double and will be returned NON-distinct */

SELECT ITEMS.*
FROM (
    SELECT GROUPS.*
    FROM @GROUP_MEMBERS AS GROUPS
    INNER JOIN GROUP_TREE ON GROUP_TREE.CHILDID = GROUPS.ID
    WHERE GROUPS.CHILDTYPE = 1

    UNION ALL

    SELECT GROUPS.*
    FROM @GROUP_MEMBERS AS GROUPS
    WHERE GROUPS.CHILDTYPE = 1
    AND GROUPS.ID = @GroupRootID
) AS GROUP_ITEMS
INNER JOIN @ITEMS AS ITEMS ON GROUP_ITEMS.CHILDID = ITEMS.ID