with_clause:
,,,WITH (递归)
,,,,,,,cte_name [(col_name [, col_name],…)], AS (子查询)
,,,,,,,(,cte_name [(col_name [, col_name],…)], AS (子查询),…
mysql>, WITH cte (col1, col2),
,,,,,(
,,,,,,,SELECT 1,, 2
,,,,,,UNION 所有
,,,,,,,SELECT 3, 4
,,,,,)
,,,,,SELECT col1,, col2 得到cte;
+ - - - - - - - - - - - - +
| |,col1 col2 |
+ - - - - - - - - - - - - +
|,,,,1,|,,,,2 |
|,,,,3,|,,,,4 |
+ - - - - - - - - - - - - +
2,rows set 拷贝;(0.00,sec)
等价与:
mysql> WITH cte
,,,,,(
,,,,,,,SELECT 1, AS col1,, 2, AS col2
,,,,,,UNION 所有
,,,,,,,SELECT 3, 4
,,,,,)
,,,,,SELECT col1,, col2 得到cte;
+ - - - - - - - - - - - - +
| |,col1 col2 |
+ - - - - - - - - - - - - +
|,,,,1,|,,,,2 |
|,,,,3,|,,,,4 |
+ - - - - - - - - - - - - +
2,rows set 拷贝;(0.00,sec)
mysql>, create table t1 (b a , int, int);
mysql>, insert into t1 价值(1,1),(2,2),(3);
mysql> with t
,,,,,,(select + 2, c, b 得到t1),
,,,,,,select c, b 得到t;
+ - - - - - - - - - - - - +
|,c ,,, |, b ,, |
+ - - - - - - - - - - - - +
|,,,,3,|,,,,1 |
|,,,,4,|,,,,2 |
|,,,,5,|,,,,3 |
+ - - - - - - - - - - - - +
3,rows set 拷贝;(0.00,sec)
mysql>, with t as (select + 2, as , b 得到t1), update t1, t set t1.a=t.a + 10, where t1.a=t.a;
mysql>, select *,得到t1;
+ - - - - - - - - - - - - +
|,a ,,, |, b ,, |
+ - - - - - - - - - - - - +
|,,,,1,|,,,,1 |
|,,,,2,|,,,,2 |
|,,,13,|,,,,3 |
+ - - - - - - - - - - - - +
3,rows set 拷贝;(0.00,sec)
mysql>, with t as (select + 2, as , b 得到t1), delete t1 得到t1, t where t1.a=t.a;
mysql>, select *,得到t1;
+ - - - - - - - - - - - - +
|,a ,,, |, b ,, |
+ - - - - - - - - - - - - +
|,,,,1,|,,,,1 |
|,,,,2,|,,,,2 |
+ - - - - - - - - - - - - +
2,rows set 拷贝;(0.00,sec)
mysql>, insert into t1 with t as (select 10 * a as , b 得到t1), select *,得到t;
mysql>, select *,得到t1;
+ - - - - - - - - - - - - +
|,a ,,, |, b ,, |
+ - - - - - - - - - - - - +
|,,,,1,|,,,,1 |
|,,,,2,|,,,,2 |
|,,,,3,|,,,,3 |
|,,,10,|,,,,1 |
|,,,20,|,,,,2 |
|,,,30,|,,,,3 |
+ - - - - - - - - - - - - +
6,rows set 拷贝;(0.00,sec)
mysql>, select count(*),得到employees e1
,,,,,,left join (select *,得到员工),e2 提醒e1.emp_no=e2.emp_no
,,,,,,left join (select *,得到员工),e3 提醒e2.emp_no=e3.emp_no;
,,,,,,
mysql>, with e2 as (select *,得到员工),
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
