问下·
select * from (select rownum rn,s.startdt,s.enddt, s.custid,cp.prodclasid,(select dictvaluea from dictionary d where cp.prodclasid=d.id)prodname,s.id,c.skjlbh, s.custname, s.custmaincode,scnameid,s.jfsk,
(select dictvaluea name from dictionary d where d.id=s.scnameid )scname, amount,invoicemoney,collectionmembid,ea.username collectionmembname,
case when (select count(*) from collectionrecords d
where d.djbh = to_char(s.id))>0 then '1' else '0' end iscollection,
djsj,machinenum,c.jssj,c.jlbh,c.cwzt
,
(select sklx
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) sklx,
(select yhmc
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) yhmc,
(select yhzh
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) yhzh
from servicechargeinfo s
left join collectionrecords c on c.djbh=to_char(s.id)
left join empmember em on em.id=s.collectionmembid
left join eauser ea on ea.userid=em.userid
left join customer cm on cm.id=s.custid
left join customerproduct cp on cp.customerid=cm.id WHERE iscancle =0
上面一段SQL语句·
怎么根据then '1' else '0' end iscollection,中的0就执行后面的 (select sklx
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) sklx,
(select yhmc
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) yhmc,
(select yhzh
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) yhzh如果是1就执行sklx,yhmc,yhzh不知道这样是否可以实现呢
selectDictionarySQL
select * from (select rownum rn,s.startdt,s.enddt, s.custid,cp.prodclasid,(select dictvaluea from dictionary d where cp.prodclasid=d.id)prodname,s.id,c.skjlbh, s.custname, s.custmaincode,scnameid,s.jfsk,
(select dictvaluea name from dictionary d where d.id=s.scnameid )scname, amount,invoicemoney,collectionmembid,ea.username collectionmembname,
case when (select count(*) from collectionrecords d
where d.djbh = to_char(s.id))>0 then '1' else '0' end iscollection,
djsj,machinenum,c.jssj,c.jlbh,c.cwzt
,
(select sklx
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) sklx,
(select yhmc
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) yhmc,
(select yhzh
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) yhzh
from servicechargeinfo s
left join collectionrecords c on c.djbh=to_char(s.id)
left join empmember em on em.id=s.collectionmembid
left join eauser ea on ea.userid=em.userid
left join customer cm on cm.id=s.custid
left join customerproduct cp on cp.customerid=cm.id WHERE iscancle =0
上面一段SQL语句·
怎么根据then '1' else '0' end iscollection,中的0就执行后面的 (select sklx
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) sklx,
(select yhmc
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) yhmc,
(select yhzh
from SERVICECHARGEINFO_SKLB sk
where s.skdept = sk.deptid) yhzh如果是1就执行sklx,yhmc,yhzh不知道这样是否可以实现呢
selectDictionarySQL
解决方案 »
- PL.SQL.Developer怎么过滤系统表只显示自己的表?
- Oracle查询问题
- oracle语句小问题
- 请问如何区分哪个用户是从哪台机器登陆ORACLE的?
- 求教~能不能在嵌套表中创建主键约束,unique,外键。
- oracle 9i从SQL SERVER 2005导入数据
- 请教一个存储过程的优化,一运行这个数据库就占用98%,我想是instr占用内存太多的原因,不知道怎么优化
- 导入导出的问题,高分求教
- 在另一库中可以引入另外一个库的数据库文件么
- 我是一个oracle初学者,安装了oracle服务器版后,却看不到数据库中的表,用toad却能看到...
- 求列转行sql
- 关于oracle rownum分页 orderby一些问题求教
可以直接全部查出来,在程序里面去判断iscollection,如果是1那么后面三个字段有效,否则直接忽视即可。
但是现在查询好慢啊,可否帮我优化一下下面这段代码
select *
from (select rownum rn,
s.startdt,
s.enddt,
s.custid,
cp.prodclasid,
(select dictvaluea
from dictionary d
where cp.prodclasid = d.id) prodname,
s.id,
c.skjlbh,
s.custname,
s.custmaincode,
scnameid,
s.jfsk,
(select dictvaluea name
from dictionary d
where d.id = s.scnameid) scname,
amount,
invoicemoney,
collectionmembid,
ea.username collectionmembname,
case
when c.jlbh is not null then
'1'
else
'0'
end iscollection,
djsj,
machinenum,
c.jssj,
c.jlbh,
c.cwzt,
case
when c.jlbh is not null then
c.sklx
else
sk.sklx
end sklx,
case
when c.jlbh is not null then
c.yhmc
else
sk.yhmc
end yhmc,
case
when c.jlbh is not null then
c.yhzh
else
sk.yhzh
end yhzh
from servicechargeinfo s
left join SERVICECHARGEINFO_SKLB sk
on s.skdept = sk.deptid
left join collectionrecords c
on c.djbh = to_char(s.id)
left join empmember em
on em.id = s.collectionmembid
left join eauser ea
on ea.userid = em.userid
left join customer cm
on cm.id = s.custid
left join customerproduct cp
on cp.customerid = cm.id
WHERE iscancle = 0
and s.skdept = '001010'
and to_char(s.id) not in
(select djbh from collectionrecords where djbh is not null)
AND s.inputdt > to_date('2013-1-1', 'YYYY-MM-DD')
and rownum <= to_number(20)
order by s.inputdt desc)
where rn > to_number('0')
left join collectionrecords c
on c.djbh = to_char(s.id)后面又有
and to_char(s.id) not in
(select djbh from collectionrecords where djbh is not null)
好像有点矛盾。