sql优化的问题!
对以下语句如何优化:
能不能直接一条语句插入;
我看了更新占用太长的时间!
或者有更好的写好!
insert into @table(boxcode,matcode,basedigit,sizecode01,sizecode02,sizecode03,sizecode04,
sizecode05,sizecode06,sizecode07,sizecode08,sizecode09,sizecode10)
select b.boxcode,b.matcode,sum(isnull(b.basedigit,0)-isnull(xdbasedigit,0)) as basedigit,
sum(case when isnull(sizecode01,0) <> 0 then isnull(sizecode01,0)-isnull(sizecodexd01,0) else 0 end) sizecode01,
sum(case when isnull(sizecode02,0) <> 0 then isnull(sizecode02,0)-isnull(sizecodexd02,0) else 0 end) sizecode02,
sum(case when isnull(sizecode03,0) <> 0 then isnull(sizecode03,0)-isnull(sizecodexd03,0) else 0 end) sizecode03,
sum(case when isnull(sizecode04,0) <> 0 then isnull(sizecode04,0)-isnull(sizecodexd04,0) else 0 end) sizecode04,
sum(case when isnull(sizecode05,0) <> 0 then isnull(sizecode05,0)-isnull(sizecodexd05,0) else 0 end) sizecode05,
sum(case when isnull(sizecode06,0) <> 0 then isnull(sizecode06,0)-isnull(sizecodexd06,0) else 0 end) sizecode06,
sum(case when isnull(sizecode07,0) <> 0 then isnull(sizecode07,0)-isnull(sizecodexd07,0) else 0 end) sizecode07,
sum(case when isnull(sizecode08,0) <> 0 then isnull(sizecode08,0)-isnull(sizecodexd08,0) else 0 end) sizecode08,
sum(case when isnull(sizecode09,0) <> 0 then isnull(sizecode09,0)-isnull(sizecodexd09,0) else 0 end) sizecode09,
sum(case when isnull(sizecode10,0) <> 0 then isnull(sizecode10,0)-isnull(sizecodexd10,0) else 0 end) sizecode10
from sWaitOrderHd a with (nolock) inner join sWaitOrderItem b with (nolock) on a.doccode=b.doccode
where a.doccode=@refcode and a.MatgroupHd= 'X ' and a.docstatus> =100
group by b.matcode,b.boxcode
order by b.matcode,b.boxcode
update @table set refcode=@refcode,matgroup=b.matgroup,oldcode=b.oldcode,colortext=b.colortext,matname=b.matname,packagecode=b.packagecode,baseuomrate=b.baseuomrate,uomrate=b.uomrate,
ratetxt=b.ratetxt,baseuom=b.baseuom,salesuom=b.salesuom,SizeTypeCode=b.SizeTypeCode,inputway=b.inputway,digit=a.basedigit*b.uomrate/b.baseuomrate,
price=b.price,totalmoney=b.totalmoney,baseprice=b.baseprice,netprice=b.netprice,netmoney=b.netmoney,vatmoney=b.vatmoney,basenetprice=b.basenetprice
from @table a join sWaitOrderItem b on a.matcode=b.matcode and a.boxcode=b.boxcode where left(b.Matgroup,1)= 'X '
[解决办法]
语句优化：
1.把INSERT INTO ...SELECT 改成SELECT ..INTO ... FROM ...
2.sum(case when isnull(sizecode01,0) <> 0 then isnull(sizecode01,0)-isnull(sizecodexd01,0) else 0 end) sizecode01
改成
sum(case when sizecode01 IS NOT NULL then sizecode01 -isnull(sizecodexd01,0) else 0 end) sizecode01
3、是于不用更新表@table表，可以合成一条INSERT INTO语句（合成有时候效率不一定很高的，需测试）。
还可以考虑先SELECT * INTO #Tmp FROM sWaitOrderHd WHERE ...把满足条件的记录存入一张临时表，再以临时表做为基数据表来对其统计。这样代码多，但速度可能会更快。
