select count(*) as succ from sp_sms_finreq where smtype in (select distinct a.smstype from ib_ru_smsrule a, ib_ru_credstatechg b where a.ruleid = b.smsruleid and b.ruletype in ('0','3','4','5','6','7','9','10','11'))怎么给改造成没有子查询,也就是如传说的分解掉?也就是说去掉select distinct a.smstype from ib_ru_smsrule a, ib_ru_credstatechg b where a.ruleid = b.smsruleid and b.ruletype in ('0','3','4','5','6','7','9','10','11')之外的括号,help!!!!!!!!!!!!!!!!!!!!
解决方案 »
- oracle10g 表闪回,提示名称为ORA-01555:快照过旧:回退段号3("SYSSMU3$")过小
- java连接Oracle 10g数据库问题
- 存储过程获取sqlcode和sqlerrm的问题
- oracle7.3到oracle8i(8.1.6)的升级
- 急急急急……!请教学过VB、VBA和ORACLE的高手们
- 零一二三四排序
- 关于sql*load的问题
- 用SQL Plus查询报ora-00942
- 救命啊!怎样解决这种死锁的情况?在线等,100分
- 关于enterprise manager..的问题。。。。
- 只想查询数据集中满足条件的前10条记录
- 查询数据特别慢,普通的一个查询也需要几十分钟
如果是从另外一个表出来,那么只能用exists来代替
--试一下:select a.count(*) succ
from sp_sms_finreq a,ib_ru_smsrule b, ib_ru_credstatechg c
where a.smtype=b.smstype and
b.ruleid = c.smsruleid and
c.ruletype in ('0','3','4','5','6','7','9','10','11');
下面是用exists代替inselect count(*) as succ from sp_sms_finreq c where exists(select 1 from ib_ru_smsrule a, ib_ru_credstatechg b where
a.smtype=c.smtype and a.ruleid = b.smsruleid and b.ruletype in ('0','3','4','5','6','7','9','10','11'))
暂时没有办法 我也是笨方法select count(*)
from sp_sms_finreq e,
(select distinct a.smstype stype
from ib_ru_smsrule a, ib_ru_credstatechg b
where a.ruleid = b.smsruleid
and b.ruletype in ('0','3','4','5','6','7','9','10','11')
)f
where e.smtype=f.stype
具体问题要具体分析
下面是分解后的SQL:
SELECT COUNT(DISTINCT a.rowid) AS SUCC
FROM SP_SMS_FINREQ A
JOIN IB_RU_SMSRULE B ON A.SMTYPE=B.SMSTYPE
JOIN IB_RU_CREDSTATECHG C B.RULEID = C.SMSRULEID
WHERE C.RULETYPE IN ('0', '3', '4', '5', '6', '7', '9', '10', '11'))
select count(s.*) as succ
from sp_sms_finreq s, ib_ru_smsrule a
where s.smtype = a.smstype
and exists
(select 1
from ib_ru_credstatechg b
where b.smsruleid=a.ruleid
and b.ruletype in ('0', '3', '4', '5', '6', '7', '9', '10', '11')))
from sp_sms_finreq As A, ( select distinct a.smstype
from ib_ru_smsrule a,
ib_ru_credstatechg b
where a.ruleid = b.smsruleid
and b.ruletype in ('0','3','4','5','6','7','9','10','11'))
) As B
where A.smstype=B.smstype
FROM ZYF_TAB1 A
JOIN ZYF_TAB2 B ON A.SMTYPE=B.RULEID
JOIN ZYF_TAB3 C ON B.RULEID=C.SMSRULEID
WHERE C.RULETYPE IN ('0', '3', '4', '5', '6', '7', '9', '10', '11')
是不是这样,运行统计是正确的,已经测试了