COMMON TABLE EXPRESSION（阿里大数据CTE用法）

MaxCompute支持SQL标准的CTE，提高SQL语句的可读性与执行效率。

命令格式

WITH 
     cte_name AS
    (
        cte_query
    )
    [,cte_name2  AS 
     (
     cte_query2
     )
    ,……]

参数说明

• cte_name：CTE的名称，不能与当前WITH子句中的其他CTE的名称相同。查询中任何使用到cte_name标识符的地方，均指CTE。
• cte_query：一个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的左表是相同的查询。通过写子查询的方式，只能重复这段代码。

使用CTE的方式重写以上语句。

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子句中指定多个子查询，像使用变量一样在整个语句中反复重用。除重用外，不必反复嵌套。