CTE表示公用表表达式,是一个临时命名结果集,用于简化SQL。MaxCompute支持标准SQL的CTE,可以更好地提高SQL语句的可读性与执行效率。本文为您介绍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
的左表是相同的查询语句。通过写子查询的方式,只能重复这段代码。
使用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
子句中指定多个子查询,像使用变量一样在整个语句中反复重用。除重用外,不必反复嵌套。