当一条SQL语句,在同一个SQL语句中多次出现时,可以使用with as 将该SQL语句作为公用SQL,之后,在下一条SQL中,使用公用SQL语句。
使用:如下
with tt as (select * from table_1);
select * from tt; -- tt代表的是公用SQL
select * from tt; -- tt 是已经定义的一张表。。
注:with as 的使用范围仅限制在,with as 紧跟着的下一条SQL。之后with as 公用SQL失效。
定义多个公用SQL,with as 使用','分割,如下:
with tt_1 as (select * from table_1) ,
tt_2 as (select * from table_2);
使用:如下
with tt as (select * from table_1);
select * from tt; -- tt代表的是公用SQL
select * from tt; -- tt 是已经定义的一张表。。
注:with as 的使用范围仅限制在,with as 紧跟着的下一条SQL。之后with as 公用SQL失效。
定义多个公用SQL,with as 使用','分割,如下:
with tt_1 as (select * from table_1) ,
tt_2 as (select * from table_2);
常用的使用方法:
insert into new_table
with ttt as (select * from table1)
select * from ttt;