溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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> WITH cte (col1, col2) AS -> ( -> SELECT 1, 2 -> UNION ALL -> SELECT 3, 4 -> ) -> SELECT col1, col2 FROM cte; +------+------+ | col1 | col2 | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec) 等價與： mysql> WITH cte AS -> ( -> SELECT 1 AS col1, 2 AS col2 -> UNION ALL -> SELECT 3, 4 -> ) -> SELECT col1, col2 FROM cte; +------+------+ | col1 | col2 | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec)
(2) CTE用在Select操作
mysql> create table t1(a int,b int); mysql> insert into t1 values(1,1),(2,2),(3,3); mysql> with t as (select a+2 c,b from t1) select c,b from t; +------+------+ | c | b | +------+------+ | 3 | 1 | | 4 | 2 | | 5 | 3 | +------+------+ 3 rows in set (0.00 sec)
(3)CTE用在DML操作
mysql> with t as (select a+2 as a,b from t1) update t1,t set t1.a=t.a+10 where t1.a=t.a; mysql> select * from t1; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | 13 | 3 | +------+------+ 3 rows in set (0.00 sec) mysql> with t as (select a+2 as a,b from t1) delete t1 from t1,t where t1.a=t.a; mysql> select * from t1; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | +------+------+ 2 rows in set (0.00 sec) mysql> insert into t1 with t as (select 10*a as a,b from t1) select * from t; mysql> select * from t1; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 10 | 1 | | 20 | 2 | | 30 | 3 | +------+------+ 6 rows in set (0.00 sec)
3、CTE可以優化SQL
(1)下面第一條SQL可以改寫成如下兩種CTE簡化形式
mysql> select count(*) from employees e1 left join (select * from employees) e2 on e1.emp_no=e2.emp_no left join (select * from employees) e3 on e2.emp_no=e3.emp_no; mysql> with e2 as (select * from employees), e3 as (select * from employees) select count(*) from employees e1 left join e2 on e1.emp_no=e2.emp_no left join e3 on e2.emp_no=e3.emp_no; mysql> with e as(select * from employees) select count(*) from employees e1 left join e e2 on e1.emp_no=e2.emp_no left join e e3 on e2.emp_no=e3.emp_no;
(2)CTE的本質是子查詢，所以子查詢的一些特性都適用，如子查詢合并。
mysql> desc with e as(select /*+ set_var(optimizer_switch='derived_merge=off')*/ * from employees) -> select count(*) from employees e1 -> left join e e2 on e1.emp_no=e2.emp_no -> left join e e3 on e2.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 | Using index | | 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 | +----+-------------+------------+------------+-------+---------------+-------------+---------+------------------+--------+----------+-------------+ 4 rows in set, 2 warnings (0.00 sec)
(3)CTE可以起到減少插入臨時表數據，優化SQL的作用
mysql> flush status; Query OK, 0 rows affected (0.02 sec) mysql> select /*+ set_var(optimizer_switch='derived_merge=off')*/ * from -> (select * from t_group) t1 -> join (select * from t_group) t2 -> on t1.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 | +--------+---------+------------+------------+--------+---------+------------+------------+ 10 rows in set (0.00 sec) mysql> show status like '%handler_write%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Handler_write | 20 | +---------------+-------+ 1 row in set (0.00 sec) mysql> flush status; Query OK, 0 rows affected (0.02 sec) mysql> with t as(select /*+ set_var(optimizer_switch='derived_merge=off')*/ * from t_group) -> select * from t t1 -> join t t2 on t1.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 | +--------+---------+------------+------------+--------+---------+------------+------------+ 10 rows in set, 1 warning (0.00 sec) mysql> show status like '%handler_write%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Handler_write | 10 | +---------------+-------+ 1 row in set (0.00 sec)
CTE除了一般功能外,還可以實現遞歸一些復雜SQL需求，參考MySQL 8.0新特性--CTE(二)
參考鏈接
13.2.13 WITH Syntax (Common Table Expressions)
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
