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;


修改上面的