MySQL with

2022-03-25 515点热度 0人点赞 0条评论

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);

Jalena

原创内容,转载请注明出处! 部分内容来自网络,请遵守法律适用!

文章评论