如何编写SQL查询找出缺失父类别关联的商品记录？

数据表结构与数据 #

Item_category表 #

category表 #

实现思路 #

1. 递归遍历类别树形结构：用递归CTE获取每个类别对应的所有父/祖先类别（包括类别自身），覆盖多级父类的场景。
2. 匹配商品应有的类别关联：将每个商品已关联的类别，和该类别所有祖先类别进行关联，得到该商品需要满足的全部类别关联关系。
3. 检测缺失的关联：通过左连接检查这些应有的关联是否在Item_category表中存在，筛选出不存在的记录。
4. 去重得到结果：对筛选出的商品id去重，得到所有存在父类别缺失的商品。

SQL查询语句 #

WITH RECURSIVE category_hierarchy AS (
    -- 初始：每个类别自身作为祖先
    SELECT id AS category_id, id AS ancestor_id
    FROM category
    UNION ALL
    -- 递归：向上遍历父类别
    SELECT ch.category_id, c.parent_id AS ancestor_id
    FROM category_hierarchy ch
    JOIN category c ON ch.ancestor_id = c.id
    WHERE c.parent_id IS NOT NULL
)
-- 筛选出缺失父类别关联的商品
SELECT DISTINCT ic.item_id
FROM Item_category ic
JOIN category_hierarchy ch ON ic.category_id = ch.category_id
LEFT JOIN Item_category ic_check 
    ON ic.item_id = ic_check.item_id 
    AND ch.ancestor_id = ic_check.category_id
WHERE ic_check.id IS NULL;

语句说明 #

• category_hierarchy 递归CTE生成每个类别对应的所有祖先节点：比如category_id=3会关联到ancestor_id=2和1，category_id=2关联到ancestor_id=1，category_id=1仅关联自身。
• 左连接ic_check用于验证当前商品是否存在对应祖先类别的关联记录，当ic_check.id为null时，说明该祖先类别的关联缺失。
• 最终通过DISTINCT去重，得到所有存在父类别缺失的商品id，示例中会返回item_id=5。

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