with...as...
需要定义一个sql片段,会将这个片段产生的结果集保存在内存中,
后续的sql均可以访问这个结果集和,作用与视图或临时表类似.
with...as...
必须和其他语句一起使用with...as...
是一次性的with...as...
的示例如下:
-- with table_name as(子查询语句) 其他sql
with tmp as (
select * from xxx
)
select * from tmp;
同级的多个临时表之间用,as
后的子句必须用()
,
with tmp1 as (
select * from xxx
),tmp2 as (
select * from xxx
)
select * from tmp1,tmp2;
with...as...
使用嵌套的例子:
with tmp2 as (
with tmp1 as (
select * from xxx
)
select * from tmp1
)
select * from tmp2;
with
子句只需要执行一次)