INSERT INTO temp1000all
SELECT
ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcBalance),0)
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcavgbalance1),0)
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddbalance1),0) as cshqsdjnc
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddbalance2),0) as cshqsdjjm
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddbalance3),0) as cshqsdjym
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddavgbalance1),0) as cshqrjjnc
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddavgbalance2),0) as cshqrjjjm
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddavgbalance3),0) as cshqrjjym
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,am.name,amg.name,am.id,amg.id
FROM Rate ra
LEFT JOIN Am_Amg_Rel aar ON aar.id = ra.am_amg_id
LEFT JOIN AccountFull af ON af.caccountNo = ra.accountNo
LEFT JOIN temp1000Rebate tempRe ON tempRe.accountNo = af.caccountNo
LEFT JOIN AccountManager am ON am.id = aar.amid
LEFT JOIN AccountManagerGroup amg ON amg.id = aar.amgid
WHERE af.cDate = '20090621'
AND af.ncustomerid <> 0
AND af.ncustomerid IS NOT NULL
AND af.citemcode IN(20202,21101,21102,21103,21104,21702)
GROUP BY am.id, amg.id, am.name, amg.name ,af.citemcode
SELECT
ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcBalance),0)
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcavgbalance1),0)
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddbalance1),0) as cshqsdjnc
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddbalance2),0) as cshqsdjjm
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddbalance3),0) as cshqsdjym
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddavgbalance1),0) as cshqrjjnc
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddavgbalance2),0) as cshqrjjjm
,ISNULL(SUM(tempRe.rebateValue * ra.RateValue / 10000 * af.dcaddavgbalance3),0) as cshqrjjym
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,am.name,amg.name,am.id,amg.id
FROM Rate ra
LEFT JOIN Am_Amg_Rel aar ON aar.id = ra.am_amg_id
LEFT JOIN AccountFull af ON af.caccountNo = ra.accountNo
LEFT JOIN temp1000Rebate tempRe ON tempRe.accountNo = af.caccountNo
LEFT JOIN AccountManager am ON am.id = aar.amid
LEFT JOIN AccountManagerGroup amg ON amg.id = aar.amgid
WHERE af.cDate = '20090621'
AND af.ncustomerid <> 0
AND af.ncustomerid IS NOT NULL
AND af.citemcode IN(20202,21101,21102,21103,21104,21702)
GROUP BY am.id, amg.id, am.name, amg.name ,af.citemcode
解决方案 »
- 单表可以用exits代替in吗
- oracle 10g 数据恢复
- oracle中哪个系统表是存储临时表的?
- oracle 11g :报错为ORA-01033 ORACLE initialization or shutdown in progress
- 新装的数据库连不上
- 求个Oracle学习群, if(LJBH=Material_code &% TPBH=TrayCode) 报错
- 缩小Oracle存储空间,not null和null
- 用过toad的朋友进来下,请教一下监控数据的问题!
- 新手上路 请多关照.介绍几本好书呗!多谢!!!
- Oracle805+WinNT4:数据文件大小不能超出4GB吗?
- 比较sql语句是否相同
- 今天的sql面试题
AND af.ncustomerid <> 0
AND af.ncustomerid IS NOT NULL
AND af.citemcode IN(20202,21101,21102,21103,21104,21702) 这些条件从af中查是多少计划,能不能把它做成一个子查询,然后再与ra左联接
可以把 AccountFull af 做成一个子查询,然后做连接,应该会快一下
不过现在的瓶颈还是在多个左连接处能不能有什么办法尽量减少左连接的数量
或者用内连接来做谢谢