hive中的with…as

with…as

子查询部分。就是查询出一部分数据保存在一个临时表temp表中

使用

最普通的嵌套查询部分

select * from table_b where id in 
         (select id from table_a where Name like 'C%')

这时候进行优化，如果嵌套过多的话，就很难阅读，建议使用变量来解决这个问题

declare @t table(id nvarchar(3))
insert into @t(id)  (select id from table_a where Name like 'C%')
select * from table_b where id in (select * from @t)

虽然上面的SQL语句比第一种方式更复杂，但是将子查询放在表变量@t中，
优点更容易维护，但是缺点是性能的损失。
由于表变量实际上使用了临时表，从而增加了额外的I/O的开销，因此，表变量的方式并不太适合数据量大且频繁查询的情况。
为此，在SQL Server2005中提供了另种方法，就是公用表表达式（CTE），使用CTE，可以使用SQL语句的可维护性，同时，CTE要比表变量的效率要高。

with cr as(
    select id from table_a where Name like 'C%'
)

select * from table_b where id in (select * from cr)

其中cr是一个公用表表达式，该表达式在使用上与表变量类似

注意事项:
1，在CTE后面必须直接跟使用CTE的SQL语句（select，insert，update等）否则CTE失效。

with cr as (
    select id from table_a where Name like 'C%'
)
select * from table_a  -- 应将这条SQL语句去掉
-- 使用CTE的SQL语句应紧跟在相关的CTE后面 --
select * from table_b where id in (select * from cr)

2，CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号隔开。

with cte1 as (
    select * from table1 where name like 'abc%'
),
cte2 as (
    select * from table2 where id > 20
),
cte3 as (
    select * from table3 where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

3，如果CTE的表名跟某表或视图重名，紧跟着的SQL仍是CTE，后面的SQL使用的是表或视图

with
table1 as
(
    select * from persons where age < 30
)
select * from table1  --  使用了名为table1的公共表表达式
select * from table1  --  使用了名为table1的数据表

4，CTE可以引用子身，也可以引用在同一WITH子句中预先定义的CTE。不允许前向引用。

5，不能在CTE_query_definition中使用以下子句
compute或compute by
order by（除非指定了top子句）
into
带有查询提示的option子句
for xml
for browse

6，如果将CTE用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾

declare @s nvarchar(3)
set @s = 'C%'
;  -- 必须加分号,需注意
with
t_tree as
(
    select CountryRegionCode from person.CountryRegion where Name like @s
)
select * from person.StateProvince where CountryRegionCode in (select * from t_tree)