递归CTE实现人员自身及继承属性查询异常求助

数据库结构与测试数据 #

CREATE TABLE tbl_Attribute 
(  
    AttributeID INT NOT NULL,  
    Name VARCHAR(100) NOT NULL 
)

INSERT INTO tbl_Attribute (AttributeID, Name) 
VALUES (1, 'Genius'), (2, 'Smart'), (3, 'Pretty'), (4, 'Ugly')

CREATE TABLE tbl_Person 
( 
    PersonID INT NOT NULL,  
    Name VARCHAR(100) NOT NULL,  
    ParentPersonID INT NULL 
)

INSERT INTO tbl_Person (PersonID, Name, ParentPersonID) 
VALUES (1, 'GrandMother', NULL), (2, 'Mother', 1), 
       (3, 'Daughter', 2), (4, 'Son', 2)

CREATE TABLE tbl_PersonAttribute 
(
    PersonID INT NOT NULL,  
    AttributeID INT NOT NULL 
)

INSERT INTO tbl_PersonAttribute (PersonID, AttributeID) 
VALUES 
  (1, 1) -- GrandMother - Genius
, (2, 2) -- Mother - Smart
, (3, 3) -- Daughter - Pretty
, (4, 4) -- Son - Ugly

原递归CTE代码（存在问题） #

原代码仅能部分获取属性，无法正确传递顶层继承属性：

WITH PersonAttrCTE AS 
(
    -- get base (parent is null)
    SELECT 
        p.PersonID,
        p.Name,
        a.Name AS AttributeName,
        CAST('Myself' AS VARCHAR(100)) [Inherit]
    FROM 
        tbl_Person p
    INNER JOIN 
        tbl_PersonAttribute pa ON p.PersonID = pa.PersonID
    INNER JOIN 
        tbl_Attribute a ON pa.AttributeID = a.AttributeID
    WHERE 
        p.ParentPersonID IS NULL

    UNION ALL

    -- get the direct attributes
    SELECT 
        p.PersonID,
        p.Name,
        a.Name AS AttributeName,
        pCTE.Name [Inherit]
    FROM 
        tbl_Person p
    INNER JOIN 
        PersonAttrCTE pCTE ON p.ParentPersonID = pCTE.PersonID
    INNER JOIN 
        tbl_PersonAttribute pa ON p.PersonID = pa.PersonID
    INNER JOIN 
        tbl_Attribute a ON pa.AttributeID = a.AttributeID

    UNION ALL

    -- get inherited attributes
    SELECT 
        p.PersonID,
        p.Name,
        a.Name AS AttributeName,
        pac.Name [Inherit]
    FROM 
        tbl_Person p
    INNER JOIN 
        PersonAttrCTE pac ON p.ParentPersonID = pac.PersonID
    INNER JOIN 
        tbl_PersonAttribute pa ON pac.PersonID = pa.PersonID
    INNER JOIN 
        tbl_Attribute a ON pa.AttributeID = a.AttributeID
)
SELECT DISTINCT
    *
FROM 
    PersonAttrCTE c
ORDER BY 
    PersonID

问题分析 #

1. 原锚点仅选取顶层无父节点的人员，遗漏了其他人员的自身属性；
2. 递归部分拆分两个UNION ALL，逻辑冗余且层级传递错误，导致顶层属性无法传递到孙辈。

正确思路：

• 锚点覆盖所有人员的自身属性；
• 递归仅处理属性继承传递：将父节点的所有属性（自身+已继承的）传递给子节点，并标记继承来源。

修正后的递归CTE代码 #

WITH PersonAttrCTE AS 
(
    -- 锚点：所有人员的自身属性
    SELECT 
        p.PersonID,
        p.Name,
        a.Name AS AttributeName,
        CAST('Myself' AS VARCHAR(100)) AS [Inherit]
    FROM 
        tbl_Person p
    INNER JOIN 
        tbl_PersonAttribute pa ON p.PersonID = pa.PersonID
    INNER JOIN 
        tbl_Attribute a ON pa.AttributeID = a.AttributeID

    UNION ALL

    -- 递归：将父节点的所有属性传递给子节点
    SELECT 
        child.PersonID,
        child.Name,
        pac.AttributeName,
        parent.Name AS [Inherit]
    FROM 
        PersonAttrCTE pac
    INNER JOIN 
        tbl_Person parent ON pac.PersonID = parent.PersonID
    INNER JOIN 
        tbl_Person child ON parent.PersonID = child.ParentPersonID
)
SELECT DISTINCT
    PersonID,
    Name,
    AttributeName,
    [Inherit]
FROM 
    PersonAttrCTE
ORDER BY 
    PersonID, 
    CASE [Inherit] WHEN 'Myself' THEN 1 ELSE 2 END -- 优先显示自身属性

预期结果 #

• GrandMother：Genius（Myself）
• Mother：Smart（Myself）、Genius（GrandMother）
• Daughter：Pretty（Myself）、Smart（Mother）、Genius（GrandMother）
• Son：Ugly（Myself）、Smart（Mother）、Genius（GrandMother）

内容的提问来源于stack exchange，提问作者Vincent Dagpin