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;
文章评论