1 CTE介绍
公用表表达式（CTE）是一个临时结果集，该结果集是从WITH子句中指定的简单查询派生而来的，该查询紧接在SELECT或INSERT关键字之前。文章来源：https://www.toymoban.com/news/detail-555960.html
CTE仅在单个语句的执行范围内定义。一个或多个CTE可以在Hive SELECT，INSERT， CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用。文章来源地址https://www.toymoban.com/news/detail-555960.html
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;到了这里，关于Hive（26）：Select高级查询之Common Table Expressions（CTE）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
