COMMON TABLE EXPRESSION（CTE） - MaxCompute

CTE表示通用資料表運算式，是一個臨時命名結果集，用於簡化SQL。MaxCompute支援標準SQL的CTE，可以更好地提高SQL語句的可讀性與執行效率。本文為您介紹CTE的功能、命令格式及使用樣本。

功能介紹

CTE可以被認為是在單個DML語句的執行範圍內定義的臨時結果集。CTE類似於派生表，它不作為Object Storage Service，並且僅在查詢期間持續。開發過程中結合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的左表是相同的查詢語句。通過寫子查詢的方式，只能重複這段代碼。

使用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子句中指定多個子查詢，像使用變數一樣在整個語句中反覆重用。除重用外，不必反覆嵌套。