MySql 8.0型特性CTE 多结果联合查询、递归查询

8.0型特性 CTE

一、非递归 CTE

1、派生表

实例

select * from (select 1) as dt;　　

2、通用表表达式（多结果联合查询）

实例

with cte as (select 1) select * from cte;　　　　　　　　　

with cte1(id) as (
	select 1 as a
),cte2 as (
	select '名称' as b
) ,cte3 as (
	select  3 as c,(select b from cte2) as d 
) 
select * from cte1 join cte3;

二.递归 CTE

语法

SELECT ... -- return initial row set

UNION ALL / UNION DISTINCT

SELECT ... -- return additional row sets

实例

with recursive cte(n) as (
 select 1
 union all
 select n + 1 from cte where n < 10
)
select * from cte;