T-SQL递归；多重递归？

Question

我正在尝试构思一个解决递归问题的方案，但我无法完全理解它。我有三个表，我们称它们为DocGroup、GroupGroup和GroupUser。在DocGroup表中，有一个分层结构的记录，指定另一个记录作为其父记录，直到一条记录成为其自身的父记录。

Doc Group
1   1
2   2
3   2
4   3
5   2
6   4

GroupGroup 包含 Group 嵌套：

Group    MemberGroup
4        2
4        1

GroupUser将一组与多个用户相关联：

Group    User   Key
1        1      ABC
1        3      BCD
1        4      CDE
2        1      DEF
2        2      EFG
2        3      FGH
3        3      GHI
4        2      HIJ
4        3      IJK
4        4      JKL

我想要指定一个用户和文档，并获取该组合的所有键的列表。例如，如果我选择用户2和文档2，我需要返回“EFG”，但是如果我选择文档6和用户3，则需要返回“IJK”、“GHI”、“FGH”和“BCD”，因为组2和1在组4中，而组4=文档4，其具有一组3。在所有情况下，我只需要获取指定用户的记录。

我想我需要多次递归才能完成这个任务，但我似乎无法在SQL中构建出这样的映射，而且我也不想使用循环和游标来完成此操作。

- cjbarth

---

你为什么说“group 4 = document 4”呢？ - ypercubeᵀᴹ

@ypercube，因为在第一个表中存在层次结构，而且Group在所有情况下都引用了Document，但并不是所有的Documents在Group字段中都有另一条记录指定的子级。 - cjbarth

如果是这种情况，而且所有文档也都是组，为什么需要两个层次结构呢？从第一个表中删除“Group”列，并将那些“Doc-Group”关系添加到第二个表中。 - ypercubeᵀᴹ

嗯，仔细想想，在一个非常复杂的场景下，这种设计有些道理。我将称Doc-Group嵌套为“绿色嵌套”，而Group-MemberGroup则为“红色嵌套”。我猜你想要的是，对于给定的用户，找到所有相关的组（使用第三个表）。然后，使用“红色”嵌套，找到所有相关组的父级（和父级的父级等等）组。然后使用“绿色”嵌套，找到这些父组的所有子级（和孙子级等等）。因此，要从用户组转到文档，您可以使用红色和绿色路径，但红色只能向上走，绿色只能向下走。对吗？ - ypercubeᵀᴹ

为了“简化管理”，既然您已经有了文档层次结构（其中一些是组），为什么还需要第二个层次结构呢？ - ypercubeᵀᴹ

Answer 1

好的，这里是答案：

DECLARE @Key varchar(max)

;WITH DocBase AS (
    SELECT Doc, Group
        FROM DocGroup
        WHERE Doc = @Doc
    UNION ALL
    SELECT DocGroup.Doc, DocGroup.Group
        FROM DocGroup INNER JOIN DocBase
            ON DocGroup.Doc = DocBase.Group
        WHERE DocBase.Doc <> DocBase.Group --to prevent infinite recursion
    ), GroupNesting AS (
    SELECT Doc
        FROM DocBase
    UNION ALL
    SELECT MemberGroup
        FROM GroupGroup.Group = GroupNesting.Doc
    ), GroupList AS (
    SELECT DISTINCT Doc
        FROM GroupNesting
    ), KeyList AS (
    SELECT Key
        FROM GroupList INNER JOIN GroupUser
            ON GroupList.Doc = GroupUser.Group
        WHERE User = @User
    )
SELECT @Key = COALESE(@Key, '') + Key
    FROM Key

SELECT @Key

我可以使用来自http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/的任何建议来完成最终拼接，但我选择了这种方法，因为它是最简单的，并且我将把它放在一个过程中。

- cjbarth

Answer 2

我必须发布这篇文章，因为我是一个自大狂:

递归的禅意

但说真的，那就是我们过去的做法。现在，你可以使用公用表达式。

希望有所帮助。

- Chris B. Behrens

---

我以前用过CTE，我相信我会使用它们来解决这个问题，但是如何使用呢？顺便说一下，那篇文章看起来很有趣。 - cjbarth