這段代碼造成的代碼慢【select tabs1.EligibleQty
from tabs tabs1
where tabs1.rn = tabs2.rn - 1
and tabs1.MO = tabs2.MO】，他的作用是為了取前一條資料的合格數量 - 當前行的完工數量 = 在制品數量
with tabs as (
select b.MO,
CompleteQty,
b.OperationNum,
b.OpDescription,
EligibleQty,
ScrapQty,
ROW_NUMBER() over(partition by b.MO order by OperationNum asc) rn
from (select b.MO,
sum(b.CompleteQty) as CompleteQty,
moo.OperationNum,
r.OpDescription,
sum(b.EligibleQty) as EligibleQty,
sum(b.ScrapQty) as ScrapQty
from MO_DispatchCompleteOrder a
left join MO_DispatchCompleteOrderDocLine b on a.ID =
b.DispatchCompleteOrderDoc
left join MO_MOOperation moo on b.MOOperation = moo.ID
left join MO_MOOperation_Trl r on moo.ID = r.ID
where moo.IsCountPoint = 1
and a.DocState = 2
group by b.MO, moo.OperationNum, r.OpDescription) b)
select mo.ID,
o.Code as OrgCode, --組織
mo.DocNo, --單據號
item.Code as ItemMasterCode, --料品
mo.StartDate, --開工時間
x.Sequence, --工序編碼
r1.Description, --工序描述
mo.ProductQty, --生產數量
ISNULL(routing.CompleteQty,0) CompleteQty, --完工數量
ISNULL(routing.EligibleQty,0) EligibleQty, --合格數量
ISNULL(routing.ScrapQty,0) ScrapQty, --報廢數量
ISNULL(routing.WorkQty,0) WorkQty--工單在制品數量
from MO_MO mo
left join CBO_ItemMaster item on mo.ItemMaster = item.ID
left join Base_Organization o on mo.Org = o.ID
left join CBO_Routing r on mo.Routing = r.ID
and r.Org = mo.Org
left join CBO_Operation x on r.ID = x.Routing
and x.Org = mo.Org
left join CBO_Operation_Trl r1 on x.ID = r1.ID
left join (
select tabs2.*,
case
when rn = 1 then
0
else
(select tabs1.EligibleQty
from tabs tabs1
where tabs1.rn = tabs2.rn - 1
and tabs1.MO = tabs2.MO) - tabs2.CompleteQty
end as WorkQty
from tabs tabs2
) routing
on mo.ID = routing.MO and x.Sequence = routing.OperationNum
where x.IsCountPoint = 1 and (routing.rn <> 1 or routing.rn is null )
order by mo.DocNo desc, x.Sequence asc
uj5u.com熱心網友回復：
sql20012+的話
lag() 函式了解一下
uj5u.com熱心網友回復：
用LAG或者LEAD函式試試uj5u.com熱心網友回復：
何不嘗試用臨時表的方式呢，把每一個Select *from table的陳述句塊執行的資料都放進臨時表，在下面用的時候直接查詢臨時表，會比你現在這種方式的效率快！轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/256970.html
標籤：應用實例
上一篇：請問，like多個值的用法
下一篇：Redis-第七章節-持久化