sql - (反向)递归查询

Question

我们有一个角色继承结构，它假设每个人都默认获得最低级别的角色，而不是最高级别的过滤，如下图所示:

role.Everyone //lowest level; everyone gets this role
  role.Applications // everyone assigned this role gets applications && everyone roles
    role.Databases // everyone assigned this role gets databases && applications && everyone roles
    role.SoftwareSubscriber
  role.Client_All // etc.
    role.Client
    role.ITClient
  role.Client
    role.NewsService // everyone assigned this role gets NewsService && Client && Everyone
                     // && Client_All roles, since Client is also a child of Client_All
    role.ClientDeliverable // etc.
  role.Employee
    role.Corporate
    role.Marketing
  role...
  ...

我会检索任何给定角色的所有“ parent ”(实际上是 child ，但无论如何)及其递归 parent 。例如，我希望查询询问 role.Databases 的 parent 返回 role.Applications和 role.Everyone .同样，我希望查询会询问 role.NewsService 的 parent 返回 role.Client , role.Everyone , 和 role.Client_All , 自 role.Client是两者的 child role.Everyone和 role.Client_All .

我尝试在 MSDN's CTE example 之后对查询建模如下，但我在获得所有递归 parent 时不知所措。谁能引导我的 CTE 查询朝着正确的方向发展？

CREATE TABLE #ATTRIBASSIGN
(
    ATTRIBID int not null
    , ITEMID int not null
    , ITEMCLASS VARCHAR(10) NOT NULL DEFAULT ('ATTRIB')
    , CONSTRAINT PK_ATTRIBASSIGN_ATTRIBID_ITEMID_ITEMCLASS PRIMARY KEY (ATTRIBID, ITEMID, ITEMCLASS)
)

CREATE TABLE #ATTRIBPROP
(
    ATTRIBID int not null identity(1,1) primary key
    , ATTRIBNAME VARCHAR(50) not null 
)
GO

INSERT INTO #ATTRIBPROP (ATTRIBNAME)
VALUES ('role.Databases'), ('role.Applications'), ('role.Everyone'), ('role.Client_All'), ('role.Employee'), ('role.SoftwareSubscriber'),
    ('role.Client'), ('role.ITClient'), ('role.NewsService'), ('role.ClientDeliverable'), ('role.Corporate'), ('role.Marketing')

GO
INSERT INTO #ATTRIBASSIGN (ATTRIBID, ITEMID)
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Everyone'
    AND B.ATTRIBNAME = 'role.Applications'
UNION   
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Everyone'
    AND B.ATTRIBNAME = 'role.Client_All'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Everyone'
    AND B.ATTRIBNAME = 'role.Client'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Everyone'
    AND B.ATTRIBNAME = 'role.Employee'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Applications'
    AND B.ATTRIBNAME = 'role.Databases'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Applications'
    AND B.ATTRIBNAME = 'role.SoftwareSubscriber'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Client_All'
    AND B.ATTRIBNAME = 'role.Client'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Client_All'
    AND B.ATTRIBNAME = 'role.ITClient'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Client'
    AND B.ATTRIBNAME = 'role.NewsService'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Client'
    AND B.ATTRIBNAME = 'role.ClientDeliverable'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Employee'
    AND B.ATTRIBNAME = 'role.Corporate'
UNION
SELECT A.ATTRIBID, B.ATTRIBID
FROM #ATTRIBPROP A
    CROSS JOIN #ATTRIBPROP B
WHERE A.ATTRIBNAME = 'role.Employee'
    AND B.ATTRIBNAME = 'role.Marketing'

GO

WITH RoleStructure (parentRole, currentRole, Level)
AS
(
    SELECT B.ITEMID, B.ATTRIBID, 0 level
    FROM #ATTRIBASSIGN B 
    WHERE B.ATTRIBID NOT IN
        (
            SELECT ITEMID
            FROM #ATTRIBASSIGN C
            WHERE B.ATTRIBID = C.ITEMID
        )
        AND B.ITEMCLASS = 'attrib'
    UNION ALL
    SELECT B.ITEMID, B.ATTRIBID, D.level - 1
    FROM #ATTRIBASSIGN B 
        INNER JOIN RoleStructure D ON B.ATTRIBID = D.parentRole
    WHERE B.ITEMCLASS = 'attrib'
)
SELECT B.ATTRIBNAME, C.ATTRIBNAME, level
FROM RoleStructure A
    INNER JOIN #ATTRIBPROP B ON A.parentRole = B.ATTRIBID
    INNER JOIN #ATTRIBPROP C ON A.currentRole = C.ATTRIBID

Answer 1

感谢您提供全面的 SQL 和示例数据 - 这使得构建答案变得更加容易!看起来你的主要错误是混淆了 parent 和 child 之间的关系。我认为你过于关注结构是如何“反向”的，并且颠覆了你的想法。我对您的 SQL 进行了两次主要编辑以使其正常工作。

1) 翻转了父项和当前项。在“AttribAssign”中，我将 ATTRIBID 视为“父项”，将“ITEMID”视为“子项”，因此您有一个很好的规则树。我还最终翻转了 UNION 的第二部分(递归部分)以排队

2) 我没有过滤“ anchor ”数据集。额外的 10 行对于保持递归按你想要的方式进行是必要的。我这样做是因为无论递归级别如何，您都希望任何父/子组合都有一行输出。您的原始表具有所有“直接”组合。您希望扩展每个“直接”以包含 N+1 级间接。您的查询给出的只是“直接”关系。通过保留所有原始链接，我们可以构建该集合以更好地找到所有链接，而不管间接级别如何。令人困惑，是的，但它有效。

;WITH RoleStructure (parentRole, currentRole, Level) 
AS 
( 
        SELECT B.ATTRIBID, B.ITEMID, 0 level 
        FROM #ATTRIBASSIGN B  
        WHERE B.ITEMCLASS = 'attrib' 

        UNION ALL 

        SELECT D.parentRole, B.ITEMID, D.level - 1 
        FROM #ATTRIBASSIGN B  
                INNER JOIN RoleStructure D ON B.ATTRIBID = D.currentRole
        WHERE B.ITEMCLASS = 'attrib' 
) 
SELECT a.parentRole, a.currentRole, B.ATTRIBNAME, C.ATTRIBNAME, level 
FROM RoleStructure A 
        INNER JOIN #ATTRIBPROP B ON A.parentRole = B.ATTRIBID 
        INNER JOIN #ATTRIBPROP C ON A.currentRole = C.ATTRIBID