SQL中的CTE

概述

CTE(Common Table Expression) ，即公用表表达式，可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE ⅥEW 语句的执行范围内定义的临时结果集。CTE与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。。

语法

WITH [RECURSIVE] expression_name[(column_name [,...])]
AS
    (CTE_definition)
SQL_statement;

多个连续

WITH cte_1[(column_name [,...])]
AS
    (cte_1_definition)，
cte_2 AS
    (cte_2_definition)
SQL_statement;

Oracle recursive with 语句不需要指定recursive关键字，可以自动识别是否recursive.Oracle 还支持CTE相关的hint.

“MATERIALIZE”告诉优化器产生一个全局的临时表保存结果，多次引用CTE时直接访问临时表即可。而”INLINE”则表示每次需要解析查询CTE

用例

非递归用法

with cte
AS(SELECT * FROM T_SomeTable)
select * from cte a inner join cte b
on a.SomeColumn= b.SomeColumn

递归用法

with  tct (id,name,parentid,levl,pa)as (
    select id, name, parentid, 0 as levl,name as pa 
    from tmp_cte_test 
    where parentid=0 
    union all 
    select tt.id, tt.name, tt.parentid, levl + 1 as levl,ts.pa||'/'||tt.name as pa
    from tmp_cte_test tt 
    inner join tct ts on ts.id = tt.parentid
) 
select * from tct;
---一些数据库中可以通过指定OPTION ( MAXRECURSION 2)来指定循环次数

sqlserver等数据库还支持DML语句

WITH moved_rows AS (
     DELETE FROM COMPANY
     WHERE41         SALARY >= 30000     
RETURNING *
 )
 INSERT INTO COMPANY1 (SELECT  FROM moved_rows);