1、CTE简介

MySQL从8.0开始支持CTE，慢慢地向Oracle学习，CTE确实是个很好用的东西，特别是针对OLAP类型的SQL,可以大大简化，优化SQL.

那么什么是CTE呢？

个人理解：CTE(common table expression)是一个临时的结果集，类似一个函数，一旦定义好，可以多次调用。

2、CTE语法

with_clause:WITH[RECURSIVE]cte_name[(col_name[,col_name]...)]AS(subquery)[,cte_name[(col_name[,col_name]...)]AS(subquery)]...

(1)列别名可以在不同的位置定义

mysql>WITHcte(col1,col2)AS->(->SELECT1,2->UNIONALL->SELECT3,4->)->SELECTcol1,col2FROMcte;+------+------+|col1|col2|+------+------+|1|2||3|4|+------+------+2rowsinset(0.00sec)等价与：mysql>WITHcteAS->(->SELECT1AScol1,2AScol2->UNIONALL->SELECT3,4->)->SELECTcol1,col2FROMcte;+------+------+|col1|col2|+------+------+|1|2||3|4|+------+------+2rowsinset(0.00sec)

(2) CTE用在Select操作

mysql>createtablet1(aint,bint);mysql>insertintot1values(1,1),(2,2),(3,3);mysql>withtas(selecta+2c,bfromt1)selectc,bfromt;+------+------+|c|b|+------+------+|3|1||4|2||5|3|+------+------+3rowsinset(0.00sec)

(3)CTE用在DML操作

mysql>withtas(selecta+2asa,bfromt1)updatet1,tsett1.a=t.a+10wheret1.a=t.a;mysql>select*fromt1;+------+------+|a|b|+------+------+|1|1||2|2||13|3|+------+------+3rowsinset(0.00sec)mysql>withtas(selecta+2asa,bfromt1)deletet1fromt1,twheret1.a=t.a;mysql>select*fromt1;+------+------+|a|b|+------+------+|1|1||2|2|+------+------+2rowsinset(0.00sec)mysql>insertintot1withtas(select10*aasa,bfromt1)select*fromt;mysql>select*fromt1;+------+------+|a|b|+------+------+|1|1||2|2||3|3||10|1||20|2||30|3|+------+------+6rowsinset(0.00sec)

3、CTE可以优化SQL

(1)下面第一条SQL可以改写成如下两种CTE简化形式

mysql>selectcount(*)fromemployeese1leftjoin(select*fromemployees)e2one1.emp_no=e2.emp_noleftjoin(select*fromemployees)e3one2.emp_no=e3.emp_no;mysql>withe2as(select*fromemployees),e3as(select*fromemployees)selectcount(*)fromemployeese1leftjoine2one1.emp_no=e2.emp_noleftjoine3one2.emp_no=e3.emp_no;mysql>witheas(select*fromemployees)selectcount(*)fromemployeese1leftjoinee2one1.emp_no=e2.emp_noleftjoinee3one2.emp_no=e3.emp_no;

(2)CTE的本质是子查询，所以子查询的一些特性都适用，如子查询合并。

mysql>descwitheas(select/*+set_var(optimizer_switch='derived_merge=off')*/*fromemployees)->selectcount(*)fromemployeese1->leftjoinee2one1.emp_no=e2.emp_no->leftjoinee3one2.emp_no=e3.emp_no;+----+-------------+------------+------------+-------+---------------+-------------+---------+------------------+--------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+-------+---------------+-------------+---------+------------------+--------+----------+-------------+|1|PRIMARY|e1|NULL|index|NULL|PRIMARY|4|NULL|299512|100.00|Usingindex||1|PRIMARY|<derived2>|NULL|ref|<auto_key0>|<auto_key0>|4|testdb.e1.emp_no|10|100.00|NULL||1|PRIMARY|<derived2>|NULL|ref|<auto_key0>|<auto_key0>|4|e2.emp_no|10|100.00|NULL||2|DERIVED|employees|NULL|ALL|NULL|NULL|NULL|NULL|299512|100.00|NULL|+----+-------------+------------+------------+-------+---------------+-------------+---------+------------------+--------+----------+-------------+4rowsinset,2warnings(0.00sec)

(3)CTE可以起到减少插入临时表数据，优化SQL的作用

mysql>flushstatus;QueryOK,0rowsaffected(0.02sec)mysql>select/*+set_var(optimizer_switch='derived_merge=off')*/*from->(select*fromt_group)t1->join(select*fromt_group)t2->ont1.emp_no=t2.emp_no;+--------+---------+------------+------------+--------+---------+------------+------------+|emp_no|dept_no|from_date|to_date|emp_no|dept_no|from_date|to_date|+--------+---------+------------+------------+--------+---------+------------+------------+|22744|d006|1986-12-01|9999-01-01|22744|d006|1986-12-01|9999-01-01||24007|d005|1986-12-01|9999-01-01|24007|d005|1986-12-01|9999-01-01||30970|d005|1986-12-01|2017-03-29|30970|d005|1986-12-01|2017-03-29||31112|d002|1986-12-01|1993-12-10|31112|d002|1986-12-01|1993-12-10||40983|d005|1986-12-01|9999-01-01|40983|d005|1986-12-01|9999-01-01||46554|d008|1986-12-01|1992-05-27|46554|d008|1986-12-01|1992-05-27||48317|d008|1986-12-01|1989-01-11|48317|d008|1986-12-01|1989-01-11||49667|d007|1986-12-01|9999-01-01|49667|d007|1986-12-01|9999-01-01||50449|d005|1986-12-01|9999-01-01|50449|d005|1986-12-01|9999-01-01||10004|d004|1986-12-01|9999-01-01|10004|d004|1986-12-01|9999-01-01|+--------+---------+------------+------------+--------+---------+------------+------------+10rowsinset(0.00sec)mysql>showstatuslike'%handler_write%';+---------------+-------+|Variable_name|Value|+---------------+-------+|Handler_write|20|+---------------+-------+1rowinset(0.00sec)mysql>flushstatus;QueryOK,0rowsaffected(0.02sec)mysql>withtas(select/*+set_var(optimizer_switch='derived_merge=off')*/*fromt_group)->select*fromtt1->jointt2ont1.emp_no=t2.emp_no;+--------+---------+------------+------------+--------+---------+------------+------------+|emp_no|dept_no|from_date|to_date|emp_no|dept_no|from_date|to_date|+--------+---------+------------+------------+--------+---------+------------+------------+|22744|d006|1986-12-01|9999-01-01|22744|d006|1986-12-01|9999-01-01||24007|d005|1986-12-01|9999-01-01|24007|d005|1986-12-01|9999-01-01||30970|d005|1986-12-01|2017-03-29|30970|d005|1986-12-01|2017-03-29||31112|d002|1986-12-01|1993-12-10|31112|d002|1986-12-01|1993-12-10||40983|d005|1986-12-01|9999-01-01|40983|d005|1986-12-01|9999-01-01||46554|d008|1986-12-01|1992-05-27|46554|d008|1986-12-01|1992-05-27||48317|d008|1986-12-01|1989-01-11|48317|d008|1986-12-01|1989-01-11||49667|d007|1986-12-01|9999-01-01|49667|d007|1986-12-01|9999-01-01||50449|d005|1986-12-01|9999-01-01|50449|d005|1986-12-01|9999-01-01||10004|d004|1986-12-01|9999-01-01|10004|d004|1986-12-01|9999-01-01|+--------+---------+------------+------------+--------+---------+------------+------------+10rowsinset,1warning(0.00sec)mysql>showstatuslike'%handler_write%';+---------------+-------+|Variable_name|Value|+---------------+-------+|Handler_write|10|+---------------+-------+1rowinset(0.00sec)

CTE除了一般功能外,还可以实现递归一些复杂SQL需求，参考MySQL 8.0新特性--CTE(二)

参考链接

13.2.13 WITH Syntax (Common Table Expressions)