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,ydlbmcwith 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,ydlbmcwith as 的语法 以及怎样使用它? 也就是在什么情况下使用? 谢谢
语法就是
with tempname as (select ....)
select ...例子:
with t as (select * from emp where depno=10)
select * from t where empno=xxx
OPER@tl> select * from test; AAA
----------
1
2
3
4OPER@tl> set autot on explain
OPER@tl> select * from (select * from test order by aaa desc)
2 where rownum=1; AAA
----------
4
Execution Plan
----------------------------------------------------------
Plan hash value: 1607412806--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (25)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 4 | 52 | 4 (25)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 4 | 52 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST | 4 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------OPER@tl> with tmp as(select * from test order by aaa desc)
2 select * from tmp where rownum=1; AAA
----------
4
Execution Plan
----------------------------------------------------------
Plan hash value: 1607412806--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (25)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 4 | 52 | 4 (25)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 4 | 52 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST | 4 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------OPER@tl>
几个表
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;
这样就可以查询出了,比较方便,没有时间建表了,自己好好看吧!
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;
修改上面的
b as(select* from )
select * from a,b where ;就是相当于内联视图了,学习了
WITH sql1 AS
(SELECT prov_id,prov_desc FROM tb_prov WHERE ROWNUM<11)
FOR cur IN(SELECT * FROM sql1) LOOP
dbms_output.put_line(cur.prov_id);
END LOOP;
END; 这段代码有什么问题,问题出在哪儿,如何解决呀