Oracle类似临时视图效果 with tmp as

因为 with tmp as 类似临时视图的效果，这里就叫临时视图吧

创建一个临时视图：

with tmp as (select sysdate nowdate from user)

 

创建多个临时视图：

with tmp_1 as (select sysdate nowdate,'啦啦啦' test from dual),
     tmp_2 as (select sysdate-2 nowdate,'啦啦啦2' test from dual),
     tmp_3 as (select sysdate-3 nowdate,'啦啦啦3' test from dual)

 

使用的好处：减少代码量，减少查询次数，提高执行效率。 

随便编一个列子：

with tmp as (
     select a.id,b.name from test001 a 
     left join test002 b on a.tid = b.id
)

select 
     b.name,
     c.name 
from test03 a
left join tmp b on a.one = b.id
left join tmp c on a.two = c.id