MySQL with

MySQL 终于在8.0支持with语法了，对于复杂查询，可以不用写那么多临时表了。

什么是With

公用表表达式（CTE）是一个临时的结果集，它存在于单个语句范围内，以后可以在该语句中引用，多次引用。

官方文档：MySQL 8.0 Reference Manual :: 13.2.15 WITH (Common Table Expressions)

语法

公用表表达式

若要指定公用表表达式，需要使用具有一个或者多个逗号分割子句的WITH字句。每个子句提供一个子查询，该子查询生产结果集，并将名称与查询相关联。

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

如果公共表达式的子查询引用其自己的名称，则该表达式就是一个递归的。如果WITH子句中的任何CTE是递归的，则必须包含关键字。

递归公用表表达式

递归公用表表达式是具有引用其自身名称的子查询表达式。

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

来个with实例

以下SQL则是一个主子表，子表为数据明细，目的是要判断子表数据的合计值与主表的总值比较，将不相同的列出来！

with t as (select bp.id, sum(bpd.payment_amount) as total
           from bus_payment_detail bpd
                    left join bus_payment bp on bp.id = bpd.parent_id
           where bp.warehousing_date between '2022-01-01' and '2022-01-31'
           group by bp.id)
select t.total, bp.total_payment, bp.id
from bus_payment bp
         left join t on t.id = bp.id
where bp.warehousing_date between '2022-01-01' and '2022-01-31'
  and t.total != bp.total_payment;

再来一个删除重复数据的，正常情况下直接使用子查询去删除数据会提示你不可执行，那么使用with作为临时表则是更好处理的。

with t as (select *
           from bus_payment
           where id not in (select max(id) as mId
                            from bus_payment
                            where warehousing_date between '2022-10-01' and '2022-10-31'
                            group by parent_id
                            having count(parent_id) > 1) -- 找出重复项目的最大Id
             and warehousing_date between '2022-10-01' and '2022-10-31') -- 排除掉最大Id获得其余的Id
update bus_payment
set is_deleted = 1
where warehousing_date between '2022-10-01' and '2022-10-31'
  -- 根据Id来批量更新
  and id in (select id from t);