select (select shortname from ldcom where comcode = v.comcode) || '____',
(select codename
from ldcode
where codetype = 'salechnl'
and code = v.code),
sum(cbcount),
nvl(sum(cbsumprem), 0),
sum(wtcount),
nvl(sum(wtsumprem), 0),
sum(ctcount),
nvl(sum(ctsumprem), 0),
sum(xtcount),
nvl(sum(xtsumprem), 0)
from (select k.comcode comcode,
k.shortname,
d.code CODE,
d.codename,
(select count(distinct l.contno)
from v_temp_bqcont l
where l.optype = 'CB'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) cbcount,
(select SUM(l.money)
from v_temp_bqcont l
where l.optype = 'CB'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) cbsumprem,
(select count(DISTINCT l.contno)
from v_temp_bqcont l
where l.optype = 'WT'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) wtcount,
-1 * (select sum(l.money)
from v_temp_bqcont l
where l.optype = 'WT'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) wtsumprem,
(select count(DISTINCT l.contno)
from v_temp_bqcont l
where l.optype = 'CT'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) ctcount,
-1 * (select sum(l.money)
from v_temp_bqcont l
where l.optype = 'CT'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) ctsumprem,
(select COUNT(DISTINCT l.contno)
from v_temp_bqcont l
where l.optype IN ('XT', 'YT')
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) xtcount,
-1 * (select sum(l.money)
from v_temp_bqcont l
where l.optype IN ('XT', 'YT')
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) xtsumprem
from ldcom k, ldcode d
where d.codetype = 'salechnl'
and k.comcode not like '8600%'
and k.comcode like '86030201%'
and d.code = '03'
and LENGTH(k.comcode) = 8
order by k.comcode, d.code) v
group by ROLLUP(v.comcode, v.code)
uj5u.com熱心網友回復：
首先：（最內層）select l.optype,l.managecom,l.salechnl,COUNT(DISTINCT l.contno) cc,sum(l.money) mm
from v_temp_bqcont l
where opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
group by l.optype,l.managecom,l.salechnl
第二層：將上述中間表與ldcom k, ldcode 關聯按條件取值；
第三層：按v.comcode, v.code分組得最終結果
另：中間層中盡量不要order by,這會增加額外開銷
uj5u.com熱心網友回復：
我滴個乖乖你這查詢列中出現了多少個子查詢，把查詢列中的子查詢使用關聯查詢按條件取值，
把你子查詢上的order by去掉，節省開銷，而且還是沒意義的開銷
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/86431.html
標籤：開發