SQL递归CTE如何同时包含关联层级与无关联Item数据？

以下是调整后的SQL语句，既能生成递归层级数据，又能将未参与任何关联的Item以指定格式纳入结果：

WITH cte AS (
    -- 初始节点：直接关联的父子关系，层级为1
    SELECT 
        a.ItemChildID, 
        a.ItemParentId, 
        1 AS Level
    FROM ItemLinksToItem a
    UNION ALL
    -- 递归遍历：向上追溯所有祖先节点，层级递增
    SELECT 
        c.ItemChildID, 
        p.ItemParentId, 
        p.Level + 1
    FROM ItemLinksToItem c
    INNER JOIN cte p ON p.ItemChildID = c.ItemParentId
)
-- 合并递归层级数据与孤立Item数据
SELECT ItemChildID, ItemParentID, Level FROM cte
UNION ALL
-- 筛选未参与任何关联的Item，构造Level=0的记录
SELECT 
    NULL AS ItemChildID, 
    i.id AS ItemParentID, 
    0 AS Level
FROM Item i
WHERE i.id NOT IN (
    SELECT ItemChildID FROM ItemLinksToItem
    UNION
    SELECT ItemParentID FROM ItemLinksToItem
);

逻辑说明 #

• 递归CTE：保留原有的递归逻辑，先提取直接的父子关联（Level=1），再递归向上遍历所有祖先节点，生成多级层级数据。
• 孤立Item处理：通过NOT IN子查询筛选出所有既未作为子项（ItemChildID）也未作为父项（ItemParentID）出现在关联表中的Item，按照要求构造列：ItemChildID设为NULL，ItemParentID使用Item自身的ID，Level固定为0。
• 结果合并：使用UNION ALL将递归层级数据和孤立Item数据合并，避免不必要的去重操作，提升查询效率。

用你提供的示例数据执行后，会得到包含所有层级数据和孤立Item的完整结果集。

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