我總覺得這樣寫不是最好，謝謝！
select bbb.ItemCode,bbb.avaliableqty,bbb.U_MaterialAttr from (
select t0.itemcode,cast(Round(sum(t1.onhand)-sum(t1.iscommited),0)as decimal(18,0)) as avaliableqty,t0.U_MaterialAttr
from OITM t0
left join OITW t1 on t0.itemcode=t1.ItemCode
where t0.validFor='Y'and t1.whscode ='F1' and t0.U_MaterialAttr in ('A','B','B-','C+')
GROUP BY T0.Itemcode,t0.U_MaterialAttr ) bbb where ( (bbb.U_MaterialAttr in ('A','B') and bbb.avaliableqty<500 ) or ( bbb.U_MaterialAttr in ('B-','C+') and bbb.avaliableqty<200 ) )
and bbb.ItemCode not in (select CCC.itemcode COLLATE Chinese_PRC_CI_AS from [testing].[dbo].[AlertItemCode] as CCC where CCC.docdate='2020-05-21' )
uj5u.com熱心網友回復：
自己改了一下，但也不知道哪個更好，或者有其他更好的select t0.itemcode,cast(Round(sum(t1.onhand)-sum(t1.iscommited),0)as decimal(18,0)) as avaliableqty,t0.U_MaterialAttr
from OITM t0
left join OITW t1 on t0.itemcode=t1.ItemCode
where t0.validFor='Y'and t1.whscode ='F1' and t0.U_MaterialAttr in ('A','B','B-','C+')
GROUP BY T0.Itemcode,t0.U_MaterialAttr having( (t0.U_MaterialAttr in ('A','B') and cast(Round(sum(t1.onhand)-sum(t1.iscommited),0)as decimal(18,0))<500 ) or (t0.U_MaterialAttr in ('B-','C+') and cast(Round(sum(t1.onhand)-sum(t1.iscommited),0)as decimal(18,0))<200 ) )
and t0.ItemCode not in (select CCC.itemcode COLLATE Chinese_PRC_CI_AS from [testing].[dbo].[AlertItemCode] as CCC where CCC.docdate='2020-05-21' )
uj5u.com熱心網友回復：
看下執行計劃，看下有多少資料量，要執行多嘗試時間，具體問題要具體分析。uj5u.com熱心網友回復：
having 和 select 嵌套 哪個更優？not in () 邏輯上能不能用其他的替換？
uj5u.com熱心網友回復：
你這種 sql ， 沒太多好優化的。先加 索引。
or 改 union all
中間結果用 臨時表 替換。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/29609.html
標籤：基礎類
下一篇：疑難問題