COMMON TABLE EXPRESSION（CTE）

功能介绍

CTE可以被认为是在单个DML语句的执行范围内定义的临时结果集。CTE类似于派生表，它不作为对象存储，并且仅在查询期间持续。开发过程中结合CTE，可以提高SQL语句可读性，便于轻松维护复杂查询。

命令格式

with 
     <cte_name> as
    (
        <cte_query>
    )
    [,<cte_name2>  as 
     (
     <cte_query2>
     )
    ,……]

• cte_name：必填。CTE的名称，不能与当前with子句中的其他CTE的名称相同。查询中任何使用到cte_name标识符的地方，均指CTE。

• cte_query：必填。一个select语句。select的结果集用于填充CTE。

使用示例

假设现有如下代码：

insert overwrite table srcp partition (p='abc')
select * from (
    select a.key, b.value
    from (
        select * from src where key is not null    ) a
    join (
        select * from src2 where value > 0    ) b
    on a.key = b.key
) c
union all
select * from (
    select a.key, b.value
    from (
        select * from src where key is not null    ) a
    left outer join (
        select * from src3 where value > 0    ) b
    on a.key = b.key and b.key is not null
)d;

顶层的union两侧各为一个join，join的左表是相同的查询语句。通过写子查询的方式，只能重复这段代码。

with 
  a as (select * from src where key is not null),
  b as (select * from src2 where value > 0),
  c as (select * from src3 where value > 0),
  d as (select a.key, b.value from a join b on a.key=b.key),
  e as (select a.key,c.value from a left outer join c on a.key=c.key and c.key is not null)
insert overwrite table srcp partition (p='abc')
select * from d union all select * from e;

重写后，a对应的子查询只需写一次，便可在后面进行重用。您可以在CTE的with子句中指定多个子查询，像使用变量一样在整个语句中反复重用。除重用外，不必反复嵌套。