昨天研究sql优化，从需要4分钟优化到不到1秒，倒腾到晚上12点多，不敢私存，贴出来大家共享一下
昨天研究sql优化，从需要4分钟优化到不到1秒，倒腾到晚上12点多，不敢私存，贴出来大家共享一下：
1 ---------------------------写法一：下面的脚本执行了4分02秒--------------------------------- 2 select shoufei.sYuYue, 3 shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu, shoufei.sPhone as sChePhone, 4 max(shoufei.dDate) as dShoufei, 5 min((case when shouFei.lRed=1 then -1 else 1 end)* 1) as nCount, 6 sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufeiList.nMoney) as nListMoney, 7 sum((case when shouFei.lRed=1 then -1 else 1 end)* 8 (shoufeiList.nEmpBonus + shoufeiList.nMoney*shoufeiList.nEmpRate) ) as nEmpBonus 9 /*sum( (case when shouFei.lRed=1 then -1 else 1 end) * 10 isNull( select sum(nEmpBonus + nMoney*nEmpRate) as nEmpBonus from vShouFeiList as list where sID=shoufei.sID), 0) 11 ) as nEmpBonus */ 12 from ShouFei /*vShouFei*/ as shoufei 13 inner join ShouFeiList as shoufeiList on shoufei.sID=shoufeilist.sID 14 where shoufei.sYuYue<>'' 15 and (shoufei.dDate>='2018-04-01' And shoufei.dDate<='2018-04-27 23:59:59') 16 group by shoufei.sYuYue, shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu,shoufei.sPhone 17 18 19 20 ---------------------写法二：速度时快时慢，快时用了3秒，慢时用了3分30秒---------------------------- 21 select shoufei.sYuYue, 22 shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu, shoufei.sPhone as sChePhone, 23 max(shoufei.dDate) as dShoufei, 24 min((case when shouFei.lRed=1 then -1 else 1 end)* 1) as nCount, 25 sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufei.nListMoney) as nListMoney, 26 sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufeiList.nEmpBonus) as nEmpBonus 27 --sum( (case when shouFei.lRed=1 then -1 else 1 end) * 28 -- isNull( (select sum(nEmpBonus + nMoney*nEmpRate) as nEmpBonus from vShouFeiList as list where sID=shoufei.sID), 0) 29 -- ) as nEmpBonus 30 -- isNull( 31 -- (select sum((case when shouFei.lRed=1 then -1 else 1 end) * (nEmpBonus + nMoney*nEmpRate)) as nEmpBonus 32 -- from vShouFeiList as list where sID=shoufei.sID), 0) as nEmpBonus 33 from ShouFei /*vShouFei*/ as shoufei 34 inner join ( 35 select sID, sum(nEmpBonus + nMoney*nEmpRate) as nEmpBonus from ShouFeiList group by sID 36 ) as shoufeiList on shoufei.sID=shoufeiList.sID 37 where 1=1 38 and shoufei.sYuYue<>'' 39 and (shoufei.dDate>='2018-04-01' And shoufei.dDate<='2018-04-27 23:59:59') 40 group by shoufei.sYuYue, shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu,shoufei.sPhone 41 42 43 44 45 --------------------------------写法三：脚本执行了不到1秒--------------------------------------------- 46 select shoufei.sYuYue, 47 shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu, shoufei.sPhone as sChePhone, 48 max(shoufei.dDate) as dShoufei, 49 min((case when shouFei.lRed=1 then -1 else 1 end)* 1) as nCount, 50 sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufei.nListMoney) as nListMoney, 51 sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufeiList.nEmpBonus) as nEmpBonus 52 --sum( (case when shouFei.lRed=1 then -1 else 1 end) * 53 -- isNull( (select sum(nEmpBonus + nMoney*nEmpRate) as nEmpBonus from vShouFeiList as list where sID=shoufei.sID), 0) 54 -- ) as nEmpBonus 55 -- isNull( 56 -- (select sum((case when shouFei.lRed=1 then -1 else 1 end) * (nEmpBonus + nMoney*nEmpRate)) as nEmpBonus 57 -- from vShouFeiList as list where sID=shoufei.sID), 0) as nEmpBonus 58 from ShouFei /*vShouFei*/ as shoufei 59 inner join ( 60 select shoufeilist.sID, sum(nEmpBonus + shoufeilist.nMoney*nEmpRate) as nEmpBonus from ShouFeiList inner join ShouFei on shoufei.sID=shoufeilist.sID 61 where shoufei.sYuYue<>'' and (shoufei.dDate>='2018-04-01' And shoufei.dDate<='2018-04-27 23:59:59') 62 group by shoufeilist.sID 63 ) as shoufeiList on shoufei.sID=shoufeiList.sID 64 where 1=1 65 -- and shoufei.sYuYue<>'' 66 -- and (shoufei.dDate>='2018-04-01' And shoufei.dDate<='2018-04-27 23:59:59') 67 group by shoufei.sYuYue, shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu,shoufei.sPhone
