SQL查找POI所属分类末级叶子节点的最佳实践探讨

先说说原SQL存在的问题：

• 混合使用旧式逗号表连接和LEFT JOIN，SQL执行优先级会导致连接逻辑不符合预期，结果可能不准确
• 硬编码三级分类关联，若分类层级超过或少于三级，该SQL直接失效
• 没有明确判断“末级叶子分类”，仅通过关联到一级分类推导，逻辑不严谨

下面是几种更优的实现方案：

方案1：递归查询适配任意分类层级 #

通过递归CTE（公共表表达式）遍历一级分类ID为1下的所有子分类，同时筛选出没有子节点的末级叶子分类，最后关联POI表，支持任意层级的分类结构：

SELECT p.*
FROM tb_poi p
INNER JOIN (
    WITH RECURSIVE category_tree AS (
        -- 起始节点：一级分类ID=1
        SELECT id, parent_id
        FROM TB_CATEGORY
        WHERE parent_id IS NULL AND id = 1
        UNION ALL
        -- 递归遍历所有子分类
        SELECT c.id, c.parent_id
        FROM TB_CATEGORY c
        INNER JOIN category_tree ct ON c.parent_id = ct.id
    )
    -- 筛选末级叶子：没有子分类的节点
    SELECT id
    FROM category_tree ct
    WHERE NOT EXISTS (
        SELECT 1 FROM TB_CATEGORY child WHERE child.parent_id = ct.id
    )
) leaf_categories ON p.CATEGORY_ID = leaf_categories.id

方案2：固定层级场景下的严谨写法 #

如果确定分类层级固定为三级，用更清晰的连接逻辑重写，同时加入叶子节点的明确判断：

SELECT p.*
FROM tb_poi p
INNER JOIN TB_CATEGORY last_cat ON p.CATEGORY_ID = last_cat.id
INNER JOIN TB_CATEGORY mid_cat ON last_cat.parent_id = mid_cat.id
INNER JOIN TB_CATEGORY first_cat ON mid_cat.parent_id = first_cat.id
WHERE first_cat.parent_id IS NULL 
  AND first_cat.id = 1
  -- 明确校验当前分类是末级叶子
  AND NOT EXISTS (SELECT 1 FROM TB_CATEGORY child WHERE child.parent_id = last_cat.id)

核心优化点 #

• 明确叶子节点判定：用NOT EXISTS校验分类是否存在子节点，比单纯关联层级更准确
• 适配动态层级：递归CTE写法无需硬编码层级数，分类结构调整时无需修改SQL
• 统一连接语法：用INNER JOIN替代混合连接写法，逻辑更清晰，避免连接优先级导致的错误

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