WITH AS的用法

1  多个临时表的用法

WITH ttx AS (SELECT * FROM tt),
          ttx1 AS (SELECT * FROM tt1)
 SELECT * FROM ttx ,ttx1;

 

2  这个可以省掉用ddl建表语句了

with test as (
select '1' id,'111' col1 ,'1234' col2 from dual union all
select '1' id,'222' col1 ,'1234' col2 from dual union all
select '1' id,'111' col1 ,'3456' col2 from dual union all
select '2' id,'111' col1 ,'345' col2 from dual union all
select '2' id,'222' col1 ,'345' col2 from dual union all
select '2' id,'222' col1 ,'322' col2 from dual)
select * from test a where (id,col2) in (select id,col2 from test b where b.col1<>a.col1)