CTE 即是 WITH 语句
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;
改造
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;