SQL子查询如何实现多级分类数据筛选-SQL-PHP中文网

子查询无法处理多级树形遍历，仅支持单层映射；多级统计必须用递归cte（如with recursive）或固定层数自连接，mysql 5.7及以下不支持递归需退化处理。

sql子查询如何实现多级分类数据筛选

子查询放WHERE里筛某一级分类，别想一次筛多级

想用子查询直接“一次性查出所有三级分类下的商品”，这条路走不通。子查询本身不处理树形结构遍历，它只解决“已知父ID集合 → 查对应子数据”这种单层映射。比如要查所有一级分类（parent_id = 0）下的商品，可以这样写：

SELECT * FROM product 
WHERE category_id IN (
  SELECT id FROM category WHERE parent_id = 0
);

但如果你把parent_id = 0换成parent_id IN (SELECT id FROM category WHERE parent_id = 0)，试图递进到二级，MySQL 5.7 会允许，MySQL 8.0+ 和 PostgreSQL 则可能因无法推导层级而返回空或报错——这不是语法错，是逻辑断层。

子查询在WHERE中只能展开一层间接关系，再深就得靠其他机制
嵌套两层IN（如 A→B→C）在老版本 MySQL 中可能跑通，但结果不可靠：若中间某层无匹配，整条链就断，且无法区分是哪一级缺失
真正需要“从根到叶子全路径”的筛选，必须先拿到完整路径集，而不是靠子查询层层跳

多级统计必须用派生表 + 别名，漏掉AS就报错

要统计每个一级分类下有多少商品、多少二级分类、多少三级分类，得把每层聚合结果分别做成派生表，再用JOIN拼起来。最常踩的坑就是子查询没加别名：

SELECT c.name, p1.cnt AS level1_cnt, p2.cnt AS level2_cnt
FROM category c
LEFT JOIN (
  SELECT parent_id, COUNT(*) AS cnt 
  FROM product 
  GROUP BY parent_id
) AS p1 ON c.id = p1.parent_id
LEFT JOIN (
  SELECT parent_id, COUNT(*) AS cnt 
  FROM category 
  WHERE parent_id != 0 
  GROUP BY parent_id
) AS p2 ON c.id = p2.parent_id;

注意：AS p1和AS p2不能省——MySQL 8.0+/PostgreSQL 会直接报ERROR 1248 (42000): Every derived table must have its own alias；SQL Server 虽容忍空格别名，但为跨平台一致，统一用AS最稳。

派生表里的GROUP BY字段必须和SELECT中非聚合字段完全一致，否则 MySQL 8.0+ 报ERROR 1055
如果category表里用parent_id = 0表示一级，那二级统计就得WHERE parent_id IN (SELECT id FROM category WHERE parent_id = 0)，不能硬写parent_id != 0
别在派生表里JOIN大表（如product连order），先聚合出id + count，外层再关联维度表

真要遍历层级，用WITH RECURSIVE，子查询顶不上

当需求明确是“列出所有分类及其深度，并统计各级商品数”，就必须放弃纯子查询思路，改用递归CTE。这是唯一能自然表达树遍历的标准方案：

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS level
  FROM category WHERE parent_id = 0
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.level + 1
  FROM category c
  INNER JOIN tree t ON c.parent_id = t.id
)
SELECT t.level, t.name, COUNT(p.id) AS product_cnt
FROM tree t
LEFT JOIN product p ON t.id = p.category_id
GROUP BY t.level, t.name;

这条语句在 PostgreSQL、SQL Server、MySQL 8.0+ 上都能跑，但 MySQL 5.7 及更早版本不支持WITH RECURSIVE，只能退回到固定层数自连接——比如最多支持三级，就得写三层LEFT JOIN，代码冗长且难维护。

递归CTE的锚点（第一部分）必须不含循环，否则会无限执行直到超限
UNION ALL比UNION快，因为不查重；分类ID天然唯一，无需去重
如果分类表里有环（比如A的parent_id指向B，B又指回A），递归会失败，上线前务必校验数据

NOT EXISTS比NOT IN安全，尤其字段可能为NULL时

筛选“从未发布过文章的分类”，容易写成WHERE id NOT IN (SELECT DISTINCT category_id FROM article)。一旦article.category_id里有NULL，整个NOT IN条件变成UNKNOWN，结果为空——不报错，也不提示，静默丢失数据。

正确写法是用NOT EXISTS：

SELECT id, name FROM category c
WHERE NOT EXISTS (
  SELECT 1 FROM article a WHERE a.category_id = c.id
);

NOT EXISTS不关心子查询返回什么值，只判断是否存在匹配行，天然绕过NULL陷阱。

EXISTS和NOT EXISTS都支持相关子查询，性能取决于category_id是否有索引
如果article表极大，而category只有几百行，NOT EXISTS比LEFT JOIN ... IS NULL更直观且通常更快
别在NOT EXISTS子查询里写SELECT *，用SELECT 1即可，减少解析开销

子查询能解决单层关联和静态集合筛选，但树形结构的动态深度、跨层聚合、路径依赖，必须交给递归CTE或应用层预计算。很多人卡在“为什么子查询嵌套三层就出错”，其实不是写法错，是问题本身超出了子查询的能力边界。