with as复习

什么是with as

with as 公用表表达式
如果一段查询的sql涉及多个重复的子查询,可以使用with as 来简化,with as 是一个简单查询的临时结果集（会把查询的表数据放到内存中，供其他查询随时使用）,oracle、hive都存在with as的用法,用于将重复的查询结果复用
可以在Hive SELECT，INSERT， CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用一个或多个。
例如

CREATE TABLE TABLE_A AS
	WITH tmp AS (
	          SELECT id,name,class FROM test
	          )
	SELECT id,name,COUNT(id) AS TIMES FROM tmp
			GROUP BY class

使用方法

1.建立一个别名

with tmp(别名) as (select * from tb_name)
select * from tmp

2.建立多个别名时,使用","连接

with tmp1 as (
select c,v,b,o from test1
),
tmp2 as(
select h,j,k,o  from test3
left join test 4
)
select a.c,a.v,a.b,x.h,x.j,h.k from
tmp1 a
left join
tmp2 x
on a.o = x.o

注意

1 with as 要和 select 一起使用，不能单独使用
2 with as 和 insert into 或者和 insert over 使用时，要放在insert 之前

错误写法：
insert into TABLE TABLE_A AS
WITH tmp AS (
SELECT id,name,class FROM test
)
SELECT id,name,COUNT(id) AS TIMES FROM tmp
GROUP BY class
会报错
while compiling statement: FAILED: ParseException line 2:0 cannot recognize input near ‘WITH’ ‘TABLE_A’ ‘AS’ in statement
正确写法：

	WITH tmp AS (
	          SELECT id,name,class FROM test
	          )
   insert into TABLE TABLE_A AS
  SELECT id,name,COUNT(id) AS TIMES FROM tmp
			GROUP BY class