MySQL的CTE(公用表表达式)
(一)概念
MySQL的CTE是在MySQL8.0版本开始支持的,公用表表达式是一个命名的临时结果集,仅在单个SQL语句(例如select、insert、delete和update)的执行范围内存在。CTE分为递归CTE和非递归CTE。
(二)意义
1、MySQL8.0之前,进行复杂查询时需要使用子查询来实现,SQL语句不仅语句复杂性能低,而且不够清晰。CTE的出现简化了复杂查询语句的编写,提高了SQL性能。
2、与子查询或者派生查询相比,CTE可以重用上次的查询结果即查询一次即可,同时,CTE可以相互引用。例如:
WITH d1 AS (SELECT ... FROM ...), d2 AS (SELECT ... FROM d1 ... ) SELECT * FROM d1, d2 ...
(三)语法如下:
with <cte_name>[(column_list)] as ( <cte_query> ) [,<cte_name2>[(column_list2)] as ( <cte_query2> ) ,……] select * from <cte_name>;
说明:
cte_name:必填。CTE的名称,不能与当前with子句中的其他CTE的名称相同。
column_list: 可选。查询中的列数必须与column_list中的列数相同。 如果省略column_list,将使用cte中查询语句中使用的列。
cte_query:必填。一个select语句。select的结果集用于填充CTE。
注意:多个cte查询语句之间用逗号分隔。
(四)CTE示例
select * from ( select a.key, b.value from ( select * from src where key is not null ) a join ( select * from src2 where value > 0 ) b on a.key = b.key ) c union all select * from ( select a.key, b.value from ( select * from src where key is not null ) a left outer join ( select * from src3 where value > 0 ) b on a.key = b.key and b.key is not null )d;
顶层的union两侧各为一个join,join的左表是相同的查询语句。通过写子查询的方式,只能重复这段代码。
使用CTE的方式重写以上语句,命令示例如下:
with a as (select * from src where key is not null), b as (select * from src2 where value > 0), c as (select * from src3 where value > 0), d as (select a.key, b.value from a join b on a.key=b.key), e as (select a.key,c.value from a left outer join c on a.key=c.key and c.key is not null)
select * from d union all select * from e;
重写后,a对应的子查询只需写一次,便可在后面进行重用。
本文来自博客园,作者:业余砖家,转载请注明原文链接:https://www.cnblogs.com/yeyuzhuanjia/p/16263224.html