Hive CTE与子查询

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;