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!!!!!!!!!!!!!!!!!!!!
如果是从另外一个表出来,那么只能用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')
是不是这样,运行统计是正确的,已经测试了