| _覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
|
FrontPage › 襯殊伎郁屋′語ろ襦
|
|
|
[edit]
1 覓語 SQL #れ螻 螳 SQL覓語 . 螳 28豐 . 貎朱Μ螳 危語 貎朱Μ襷 覃 ろ 覯覯り 一 .
select distinct Case A.InoutGubun When 'SO' then A.InoutNo else A.OriginNo End as InoutNo
, A.InoutDate
,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = B.WhCode)
,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = A.InoutPlace)
,A.InoutPlace , A.SiteCode , B.WhCode
from mmInoutHeader A join mmInOutItem B
on A.SiteCode=B.SiteCode
and A.InoutNo = B.InoutNo
and A.InoutGubun=B.InoutGubun
where A.SiteCode = N'N100'
and A.inoutGubun = N'SO'
and A.SysCase = N'400'
And A.CaseCode = N'400'
and A.InOutPlace in ( select WhCode
From WhMaster
where ComCode = N'NXN1'
and C_DeptCode = N'11403'
and WhUse=N'Y'
and WhType = 'SM' and SiteCode = N'N100' )
and B.InoutQty - (select isnull(SUM(D.InoutQty),0) as InWhQty
from mminoutHeader C join mmInOutItem D
on C.SiteCode = D.SiteCode
and C.InOutNo = D.InOutNo
and C.InoutGubun=D.InoutGubun
where C.SiteCode = A.SiteCode
and C.OriginNo = A.InoutNo
and D.TrackingAltKey = B.InoutSerNo ) > 0
order by A.InoutNo
go
貎朱Μ襯 所 れ 燕覃 れ螻 螳.
select distinct Case A.InoutGubun When 'SO' then A.InoutNo else A.OriginNo End as InoutNo
, A.InoutDate
,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = B.WhCode)
,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = A.InoutPlace)
,A.InoutPlace , A.SiteCode , B.WhCode
from mmInoutHeader A join mmInOutItem B
on A.SiteCode=B.SiteCode
and A.InoutNo = B.InoutNo
and A.InoutGubun=B.InoutGubun
where A.SiteCode = N'N100'
and A.inoutGubun = N'SO'
and A.SysCase = N'400'
And A.CaseCode = N'400'
and A.InOutPlace in ( select WhCode
From WhMaster
where ComCode = N'NXN1'
and C_DeptCode = N'11403'
and WhUse=N'Y'
and WhType = 'SM' and SiteCode = N'N100' )
and B.InoutQty > (select isnull(SUM(D.InoutQty),0) as InWhQty
from mminoutHeader C join mmInOutItem D
on C.SiteCode = D.SiteCode
and C.InOutNo = D.InOutNo
and C.InoutGubun=D.InoutGubun
where C.SiteCode = A.SiteCode
and C.OriginNo = A.InoutNo
and D.TrackingAltKey = B.InoutSerNo )
order by A.InoutNo
go
貎朱Μ 蟯觚貎朱Μ襦 覃語深襴 蟆郁骸 1蟇伎 覦 觚貎朱Μ(蠍) 譟郁唄 襷蟆 豌襴襯 蟆 朱企. 蠏碁 貎朱Μ 觚貎朱Μ 覃語深襴襯 螳螳 襴曙朱 豌襴 Hash Join 蟆 . 蠏碁覩襦 觚貎朱Μ mminoutHeader 企 1404720蟇願骸 mmInOutItem 企 1874368蟇伎 螳螳 曙 Merge Join 襦 襴 讌螻襯 . 貎朱Μ 貎朱Μ 觜 襷れ 蠍 覓語(100伎) 覲貎朱Μ襯 CPU 襷 蟆 . 貎朱Μ 螻 螳 れ螻 螳.
'WhMaster' 企. れ 8, 朱Μ 所鍵 24, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0. 'mmInoutItem' 企. れ 428, 朱Μ 所鍵 181544, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0. 'mmInoutHeader' 企. れ 1474, 朱Μ 所鍵 312090, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0. 貎朱Μ 覓語 一危 郁屋 覓語. 一危磯ゼ るジ 覦朱 郁屋 覃語深襴 蟆郁骸襯 覦 碁煙るゼ 讌螻る 觜襯 蟆郁骸襯 覲 . 企 蟆曙磯 襯 一危磯ゼ 郁屋る 蟆 螻殊企. 蠏碁覩襦 れ螻 螳 ロ襯 燕.
create function fn_InWhQty (@SiteCode varchar(255), @InoutNo varchar(255), @InoutSerNo varchar(10) )
returns numeric
AS
begin
declare @rs numeric
begin
select @rs = isnull(SUM(D.InoutQty),0)
from mminoutHeader C inner loop join mmInOutItem D
on C.SiteCode = D.SiteCode
and C.InOutNo = D.InOutNo
and C.InoutGubun=D.InoutGubun
where C.SiteCode = @SiteCode
and C.OriginNo = @InoutNo
and D.TrackingAltKey = @InoutSerNo
return @rs
end
end
go
襯 伎 觚貎朱Μ襯 豌襴 蟆郁骸 れ螻 螳.
select distinct Case A.InoutGubun When 'SO' then A.InoutNo else A.OriginNo End as InoutNo , A.InoutDate ,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = B.WhCode) ,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = A.InoutPlace) ,A.InoutPlace , A.SiteCode , B.WhCode from mmInoutHeader A join mmInOutItem B on A.SiteCode= B.SiteCode and A.InoutNo = B.InoutNo and A.InoutGubun=B.InoutGubun and A.SiteCode = N'N100' and A.inoutGubun = N'SO' and A.SysCase = N'400' And A.CaseCode = N'400' join WhMaster c on A.InOutPlace = c.WhCode and c.ComCode = N'NXN1' and c.C_DeptCode = N'11403' and c.WhUse=N'Y' and c.WhType = 'SM' and c.SiteCode = N'N100' and B.InoutQty > xerp.dbo.fn_InWhQty(a.SiteCode, A.InoutNo, B.InoutSerNo) order by A.InoutNo GO
'mmInoutItem' 企. れ 173, 朱Μ 所鍵 852, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0. 'mmInoutHeader' 企. れ 1, 朱Μ 所鍵 30691, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0. 'WhMaster' 企. れ 3, 朱Μ 所鍵 9, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0. れ襦 覈 豌蟇伎 豌襴 覿蟲螻 貎朱Μ 1覦 50襷蟇 襯 豌襴朱 豌襴襯 螻 蟆 覓語.
鏤
|
覯碁 伎朱襦 蟾讌 讌 狩 覦覯企. |