CTE层级数据查询遗漏合法叶子节点的问题排查及优化

我有一张存储层级数据的表，每条记录的parent_item_code字段引用同表中的其他记录。我使用Common Table Expression (CTE)构建每个项的完整路径，拼接父节点名称直至叶子节点。

原查询语句 #

WITH Hierarchy AS (
  -- 选择根节点（无父节点的记录）
  SELECT 
     i.item_code,
     i.name,
     i.parent_item_code,
      CAST(i.name AS VARCHAR(1000)) AS hierarchy
  FROM sgt_consulta.items i
  WHERE i.item_type = 'C' 
      AND i.status = 'A' 
      AND i.parent_item_code IS NULL  -- 仅根节点

  UNION ALL

  -- 遍历子节点并拼接层级路径
  SELECT
      c.item_code,
      c.name,
      c.parent_item_code,
      CAST(h.hierarchy + ' > ' + c.name AS VARCHAR(1000)) AS hierarchy
  FROM sgt_consulta.items c
  INNER JOIN Hierarchy h ON c.parent_item_code = h.item_code
  WHERE c.item_type = 'C' 
    AND c.status = 'A'
)
-- 筛选叶子节点（无子节点的记录）
SELECT 
  h.item_code,
  h.name,
  h.hierarchy
FROM Hierarchy h
LEFT JOIN sgt_consulta.items i ON h.item_code = i.parent_item_code
WHERE i.parent_item_code IS NULL;  -- 仅最终节点

问题现象 #

该查询对大多数记录有效，但部分符合以下条件的记录被遗漏：

• 拥有完整的层级结构，祖先节点结构正确；
• item_code、parent_item_code、name等重要字段无NULL值；
• 本应被识别为叶子节点，却被排除在外。

移除最后的WHERE i.parent_item_code IS NULL条件后，遗漏的记录会重新出现，但结果会包含非叶子节点，不符合需求。

示例数据 #

正常返回的记录（首行为叶子节点） #

被遗漏的叶子节点记录（首行为被排除的叶子节点） #

问题原因 #

原查询筛选叶子节点的逻辑存在漏洞：

LEFT JOIN sgt_consulta.items i ON h.item_code = i.parent_item_code
WHERE i.parent_item_code IS NULL;

这里的LEFT JOIN没有过滤子节点的item_type和status条件。如果某个节点h存在状态为非'A'或类型非'C'的子节点，这些子节点不会出现在CTE的Hierarchy结果中，但会在最后的LEFT JOIN中被匹配到，导致i的记录存在，i.parent_item_code不为NULL，从而h被错误地排除。

可靠的叶子节点筛选方法 #

方法1：在LEFT JOIN时过滤有效子节点 #

在JOIN的关联条件中添加子节点必须满足item_type='C'和status='A'的限制，仅考虑有效子节点：

WITH Hierarchy AS (
  -- 选择根节点（无父节点的记录）
  SELECT 
     i.item_code,
     i.name,
     i.parent_item_code,
      CAST(i.name AS VARCHAR(1000)) AS hierarchy
  FROM sgt_consulta.items i
  WHERE i.item_type = 'C' 
      AND i.status = 'A' 
      AND i.parent_item_code IS NULL  -- 仅根节点

  UNION ALL

  -- 遍历子节点并拼接层级路径
  SELECT
      c.item_code,
      c.name,
      c.parent_item_code,
      CAST(h.hierarchy + ' > ' + c.name AS VARCHAR(1000)) AS hierarchy
  FROM sgt_consulta.items c
  INNER JOIN Hierarchy h ON c.parent_item_code = h.item_code
  WHERE c.item_type = 'C' 
    AND c.status = 'A'
)
-- 筛选叶子节点（无有效子节点的记录）
SELECT 
  h.item_code,
  h.name,
  h.hierarchy
FROM Hierarchy h
LEFT JOIN sgt_consulta.items i 
  ON h.item_code = i.parent_item_code
  AND i.item_type = 'C' 
  AND i.status = 'A'  -- 仅考虑有效子节点
WHERE i.item_code IS NULL;  -- 没有匹配到有效子节点

方法2：在CTE中直接标记叶子节点 #

在生成层级数据时，通过EXISTS判断当前节点是否有有效子节点，直接标记叶子状态：

WITH Hierarchy AS (
  -- 选择根节点并标记是否为叶子
  SELECT 
     i.item_code,
     i.name,
     i.parent_item_code,
     CAST(i.name AS VARCHAR(1000)) AS hierarchy,
     -- 检查是否有有效子节点
     CASE WHEN EXISTS (
         SELECT 1 FROM sgt_consulta.items c 
         WHERE c.parent_item_code = i.item_code 
           AND c.item_type = 'C' 
           AND c.status = 'A'
     ) THEN 0 ELSE 1 END AS is_leaf
  FROM sgt_consulta.items i
  WHERE i.item_type = 'C' 
      AND i.status = 'A' 
      AND i.parent_item_code IS NULL

  UNION ALL

  -- 遍历子节点并标记是否为叶子
  SELECT
      c.item_code,
      c.name,
      c.parent_item_code,
      CAST(h.hierarchy + ' > ' + c.name AS VARCHAR(1000)) AS hierarchy,
      CASE WHEN EXISTS (
          SELECT 1 FROM sgt_consulta.items sub_c 
          WHERE sub_c.parent_item_code = c.item_code 
            AND sub_c.item_type = 'C' 
            AND sub_c.status = 'A'
      ) THEN 0 ELSE 1 END AS is_leaf
  FROM sgt_consulta.items c
  INNER JOIN Hierarchy h ON c.parent_item_code = h.item_code
  WHERE c.item_type = 'C' 
    AND c.status = 'A'
)
-- 直接筛选叶子节点
SELECT 
  h.item_code,
  h.name,
  h.hierarchy
FROM Hierarchy h
WHERE h.is_leaf = 1;

这种方法逻辑更清晰，避免了额外的JOIN操作，筛选效率更高。

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