sql with怎么用，cte公用表表达式写法

一条查询里同一个子查询要用三遍，复制粘贴三遍又长又难改。sql with 就是解决这个的：先用 with 定义一个临时结果集，后面的主查询直接引用它，像变量一样。

基本写法

WITH hot AS (SELECT product_id, SUM(qty) AS total_qty FROM orders GROUP BY product_id) SELECT product_id, total_qty FROM hot WHERE total_qty > 100;

WITH 后面是名字加 AS 加括号里的查询，主查询跟在后面，直接 FROM 这个名字。上面的语句先把销量汇总好，再筛出大于 100 的商品，逻辑拆成两步，可读性好很多。

多个 cte 和嵌套

几个临时结果集用逗号隔开：

WITH a AS (SELECT id FROM dept WHERE level = 1), b AS (SELECT id FROM dept WHERE city = 1) SELECT * FROM a JOIN b USING (id);

后面的 cte 还能引用前面定义的 cte，一层层往上搭。复杂报表的 SQL 建议都这么分层写，排查问题也容易，哪一段不对单独看哪一段。

递归查询：WITH RECURSIVE

查组织架构树、分类层级这种，普通查询写不出来，递归 cte 一条搞定：

WITH RECURSIVE tree AS (SELECT id, parent_id, 1 AS depth FROM dept WHERE id = 1 UNION ALL SELECT d.id, d.parent_id, tree.depth + 1 FROM dept d JOIN tree ON d.parent_id = tree.id) SELECT * FROM tree;

从指定节点出发，沿 parent_id 一层层往下找，UNION ALL 后半段是递归部分。注意递归必须有终止条件，条件写错轻则报错退出，重则无限循环。

版本和性能

MySQL 8.0 才支持 with 语法，5.7 及以下直接报语法错误；SQL Server、PostgreSQL、Oracle 早就支持。性能上，MySQL 对 cte 的优化和子查询差别不大，该建索引照样建，别指望换个写法就飞快。

判断标准很简单：子查询重复出现，或者逻辑超过三层嵌套，就换成 with 分步写；单表单条件的小查询，没必要硬套。