SQL Server如何用递归CTE获取含指定属性的所有原生与派生对象？

你说得没错，递归CTE（Common Table Expression）正是处理这种无限嵌套对象层级、获取继承属性对象的完美工具。针对你的需求，我会给出两种可行的实现思路，帮你轻松拿到想要的结果。

先回顾你的数据结构 #

首先确认一下你提供的表结构和数据：

CREATE TABLE OBJECTS ( [ID] INT, [PARENTID] INT, [ObjectName] VARCHAR(32) );
INSERT INTO OBJECTS ([ID], [PARENTID], [ObjectName]) 
VALUES (1, 0, 'Parent1'), (2, 1, 'Parent2'), (3, 1, 'Item1'), (4, 1, 'Item2'), (5, 2, 'Item3'), (6, 0, 'Item4'), (7, 0, 'Item5');

CREATE TABLE ATTRIBUTES ( [ID] INT, [AttributeName] VARCHAR(1) );
INSERT INTO ATTRIBUTES ([ID], [AttributeName]) 
VALUES (1, 'A'), (1, 'B'), (2, 'C'), (2, 'D'), (3, 'F'), (6, 'C'), (7, 'A');

方案一：从有目标属性的对象向下遍历所有派生对象 #

这种方法效率更高，因为我们只从直接拥有属性'A'的对象出发，逐层向下抓取所有子对象（包括多层嵌套的派生对象）。

实现代码 #

WITH AttributeObjects AS (
    -- 锚点：直接拥有属性'A'的对象
    SELECT o.ID, o.ObjectName, o.PARENTID
    FROM OBJECTS o
    INNER JOIN ATTRIBUTES a 
        ON o.ID = a.ID
    WHERE a.AttributeName = 'A'
    
    UNION ALL
    
    -- 递归：抓取当前对象的所有子对象
    SELECT o.ID, o.ObjectName, o.PARENTID
    FROM OBJECTS o
    INNER JOIN AttributeObjects ao 
        ON o.PARENTID = ao.ID
)
-- 去重后输出（避免同一对象通过多条路径被选中）
SELECT DISTINCT ID, ObjectName
FROM AttributeObjects
ORDER BY ID;

代码解释 #

1. 锚点成员：先筛选出所有直接在ATTRIBUTES表中拥有'A'属性的对象（这里是ID=1和ID=7）。
2. 递归成员：将OBJECTS表和递归结果关联，找到每个已选中对象的子对象（通过PARENTID匹配），逐层遍历所有嵌套的派生对象。
3. 最终查询：用DISTINCT去重（防止某些对象通过多个父级路径被重复选中），再按ID排序输出。

运行结果 #

ID  OBJECTNAME
1   Parent1
2   Parent2
3   Item1
4   Item2
5   Item3
7   Item5

完全符合你的期望输出！

方案二：检查每个对象的父链是否包含有目标属性的对象 #

如果你需要验证每个对象的整个父级链条是否存在目标属性，这种思路更直观：对每个对象，向上遍历所有父级，只要自身或任意父级有属性'A'，就保留该对象。

实现代码 #

WITH ObjectHierarchy AS (
    -- 锚点：所有对象，标记自身是否有属性'A'
    SELECT 
        o.ID, 
        o.ObjectName, 
        o.PARENTID,
        CASE 
            WHEN EXISTS (SELECT 1 FROM ATTRIBUTES a WHERE a.ID = o.ID AND a.AttributeName = 'A') 
            THEN 1 
            ELSE 0 
        END AS HasTargetAttribute
    FROM OBJECTS o
    
    UNION ALL
    
    -- 递归：向上遍历父级，更新属性标记
    SELECT 
        oh.ID, 
        oh.ObjectName, 
        o.PARENTID,
        CASE 
            WHEN oh.HasTargetAttribute = 1 OR EXISTS (SELECT 1 FROM ATTRIBUTES a WHERE a.ID = o.ID AND a.AttributeName = 'A') 
            THEN 1 
            ELSE 0 
        END AS HasTargetAttribute
    FROM ObjectHierarchy oh
    INNER JOIN OBJECTS o 
        ON oh.PARENTID = o.ID
    WHERE oh.HasTargetAttribute = 0 -- 只处理还没找到目标属性的对象
)
-- 筛选出拥有目标属性（含继承）的对象
SELECT DISTINCT ID, ObjectName
FROM ObjectHierarchy
WHERE HasTargetAttribute = 1
ORDER BY ID;

代码解释 #

1. 锚点成员：先给所有对象初始化标记，判断自身是否直接拥有属性'A'。
2. 递归成员：对还没找到目标属性的对象，向上遍历父级，只要父级有属性'A'，就把标记更新为1，直到遍历到根节点（PARENTID=0）。
3. 最终查询：筛选出标记为1的对象，去重后输出。

两种方案对比 #

• 方案一：性能更优，因为只从有目标属性的对象出发向下遍历，不需要处理所有对象，适合大数据量场景。
• 方案二：逻辑更直观，便于理解每个对象的属性继承路径，适合需要验证父链的场景。

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