CTE 功能上和子查询一样,可读性好些 mysql8才支持
Common Table Expression(公用表表达式)
从 WITH 子句中指定的简单查询派生的临时结果集,在 SELECT 或 INSERT 关键字之前。 Hive SELECT、INSERT、 CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中可以使用一个或多个 CTE 。
hive>set hive.exec.mode.local.auto=true; //设置本地模式
在linux本地下vi test.txt
1,'zhangsan','2020-12-12',NULL,10
2,'lisi','2020-12-12',1600,20
3,'wangwu','2020-12-12',500,30
4,'zhangsan2','2020-12-12',NULL,20
5,'lisi2','2020-12-12',1250,20
6,'wangwu2','2020-12-12',NULL,30
hive>CREATE TABLE tab(
emp_no string,
name string,
hire_date string,
comm int,
deptno string
)
row format delimited
fields terminated by ',';
load data local inpath '/root/data/test.txt' into table tab;
在linux本地下vi test2.txt
10,开发
20,项目
30,运维
hive>CREATE TABLE tab2(
deptno string,
department string
)
row format delimited
fields terminated by ',';
load data local inpath '/root/data/test2.txt' into table tab2;
子查询(3层嵌套):
select tt.*,d.department
from
(
select
e.*
from tab e join
(
select
deptno,
max(comm) max_comm
from tab
group by deptno
)t
on (t.deptno=e.deptno and e.comm=t.max_comm)
)tt
join tab2 d
on tt.deptno=d.deptno;
CTE(3层嵌套):
with temp as(
select
deptno,
max(comm) max_comm
from tab
group by deptno
),
result as(
select
e.*
from tab e join temp t
on (t.deptno=e.deptno and e.comm=t.max_comm)
)
select r.*,tt.department
from result r join tab2 tt
on r.deptno=tt.deptno;