hive 中使用with…as语法建立临时中间表

当我们书写一些结构相对复杂的SQL语句时，可能某个子查询在多个层级多个地方存在重复使用的情况，这个时候我们可以使用 with as 语句将其独立出来，极大提高SQL可读性，简化SQL。前mysql、 oracle、sql server、hive等均支持 with as 用法

1、介绍

with as 也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union等集合操作中。
with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名，不同的是with as 属于一次性的，而且必须要和其他sql一起使用才可以！
其最大的好处就是适当的提高代码可读性，而且如果with子句在后面要多次使用到，这可以大大的简化SQL；更重要的是：一次分析，多次使用，这也是为什么会提供性能的地方，达到了“少读”的目标。

with sub_query_name1 as (
	select 
		xx
	from
		xxx
), -- 注意这里用逗号分割
sub_query_name2 as ( --注意这里没有as，上面一个as跟多个sub_query
	select
		xx
	from
		xx
)
select --正式查询语句，与上面的with as直接没有符号分割
	xx
from
	sub_query_name1,sub_query_name2

2、注意事项

• 整体作为一条sql查询，即with as语句后不能加分号，不然会报错。
• with子句必须在引用的select语句之前定义,同级with关键字只能使用一次,多个只能用逗号分割；最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来.
• 如果定义了with子句，但其后没有跟select查询，则会报错！
• 前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句！

3、代码示例

# 直接查询
with tmp_a as (
select f1,f2,f3 from test1
)
select f1,f2,f3 from tmp_a;

# 多表关联
with tmp_a as (
select f1,f2,f3 from test1
),
tmp_b as(
select f1,f4,f5 from test2
)
select a.f1,a.f2,a.f3,b.f4,b.f5 from
tmp_a a
left join
tmp_b b
on a.f1 = b.f1