oracle with as 的用法多谢_Oracle管理

oracle with as 的用法谢谢?
with tmp1 as (
SELECT
case when (select org_type from epma.o_org where org_no=a.org_no)='6'
then (select p_org_no from epma.o_org where org_no=a.org_no)
else a.org_no end org_no,
case when (ELEC_TYPE_CODE IN('100','101','102') ) then '1、大工业 '
when (ELEC_TYPE_CODE IN('402','40304') ) then '2、非普工业'
when (ELPE_CODE IN('300','301','302') ) then '3、业 '
when (ELEC_TYPE_CODE IN('400','401','203') ) then '4、其它照明'
when (ELEC_TYPE_CODE 00','201','202') ) then '5、居民照明'
hen (ELEC_TYPE_CODE IN('405') ) then '6、商业照明'
when (ELEC_TYPE_CODE IN('500','501'','503','504','505') ) then '7、趸售 '
when (ELEC_T_CODE IN('000','900') ) then '8、其 '
end ydlbmc,count(*) hs,sum(CONTRACT_CAP) as rl
from epma.c_cons a where
STATUS_CODE<> '9'
AND MR_SECT_NO <> '9999999999'
AND BUILD_DATE < to_date('2008-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND CANCEL_DATE > to_date('2008-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND NOT EXISTS (SELECT 1
FROM EPMA.C_MP B
WHERE B.CONS_ID = A.CONS_ID AND B.TYPE_CODE = '02')
group by org_no,ELEC_TYPE_CODE)
select (select org_name from epma.o_org where org_no=tmp1.org_no),ydlbmc,tmp1.hs,tmp1.rl from tmp1
order by tmp1.org_no,ydlbmc

with as 的语法以及怎样使用它? 也就是在什么情况下使用? 谢谢

------解决方案--------------------
与内联视图作用完全等价。
------解决方案--------------------
相当于建个临时表
语法就是
with tempname as (select ....)
select ...

例子：
with t as (select * from emp where depno=10)
select * from t where empno=xxx

------解决方案--------------------
是个临时存储,一般是在存储过程里使用的
------解决方案--------------------
知道怎么用就OK了。主要是方便写sql的。
------解决方案--------------------

探讨
内联视图是什么?谢谢

------解决方案--------------------
可以做多个表的连接，结果集的连接查询

比如查询某个emp、dept、还有work表 work表是用来存储工作信息的，eid,did,salary等

要求查询工资比起所在部门工资高的员工的信息，此时需要很多的连接，就可以使用with as 比较清晰的连接
几个表

Assembly code

with wd as (select did,arg(salary) 平均工资 from work),em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)select * from wd,em where wd.did =em.did and wd.平均工资>em.salary;
------解决方案--------------------
with  
wd as (select did,arg(salary) 平均工资 from work group by did),
em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)

select * from wd,em where wd.did =em.did and wd.平均工资>em.salary;


修改上面的

oracle with as 的用法 多谢

oracle with as 的用法多谢