<正则吃饺子> ：关于oracle 中 with的简单使用

oracle中 with的简单使用介绍，具体可以参见其他的博文介绍，在这里只是简单的介绍：

with 构建了一个临时表，类似于存储过程中的游标，我是这么理解的。

一、数据准备：

select * from table1;

insert into table1(id) values(1);
insert into table1(id) values(2);
insert into table1(id) values(3);
insert into table1(id) values(4);

select * from table2;

insert into table2(id) values(5);
insert into table2(id) values(6);
insert into table2(id) values(7);
insert into table2(id) values(8);

二、测试

with
sql1 as (select * from table1),
sql2 as (select * from table2)
select * from sql1
union
select * from sql2;

--///////////////////////
-- 网上的博文说，这里会报错，我在本地测试，并没有。
with
sql1 as (select * from table1),
sql2 as (select * from table2)
select * from table1
union
select * from table2;

--////

with
sql1 as (select * from table1),
sql2 as (select * from table2)
select * from sql1
where id in(1,2)
union
select * from sql2
where id in(6,9);

--/////

with
q1 as (select 3+5 s from dual) ,
q2 as (select 3*5 m from dual),
q3 as (select s,m,s+m,s*m from q1,q2)   
select * from q3 ;

--简单整理，做个记录吧。

 

转载于:https://www.cnblogs.com/zhengzeze/p/7359485.html