唐山市路桥建设有限公司网站,上海seo网站,中国建设银行官网站纪念币河南,设计软件免费下载官方网站1 CTE介绍
公用表表达式#xff08;CTE#xff09;是一个临时结果集#xff0c;该结果集是从WITH子句中指定的简单查询派生而来的#xff0c;该查询紧接在SELECT或INSERT关键字之前。
CTE仅在单个语句的执行范围内定义。一个或多个CTE可以在Hive SELECT#xff0c;INSER…1 CTE介绍
公用表表达式CTE是一个临时结果集该结果集是从WITH子句中指定的简单查询派生而来的该查询紧接在SELECT或INSERT关键字之前。
CTE仅在单个语句的执行范围内定义。一个或多个CTE可以在Hive SELECTINSERT CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用。 2 CTE案例
--选择语句中的CTE
with q1 as (select sno,sname,sage from student where sno 95002)
select *
from q1;-- from风格
with q1 as (select sno,sname,sage from student where sno 95002)
from q1
select *;-- chaining CTEs 链式
with q1 as ( select * from student where sno 95002),q2 as ( select sno,sname,sage from q1)
select * from (select sno from q2) a;-- union案例
with q1 as (select * from student where sno 95002),q2 as (select * from student where sno 95004)
select * from q1 union all select * from q2;--视图CTAS和插入语句中的CTE
-- insert
create table s1 like student;with q1 as ( select * from student where sno 95002)
from q1
insert overwrite table s1
select *;select * from s1;-- ctas
create table s2 as
with q1 as ( select * from student where sno 95002)
select * from q1;-- view
create view v1 as
with q1 as ( select * from student where sno 95002)
select * from q1;select * from v1;
