Contents

1 覓語 SQL
2 / 觜蟲襭


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 襷 蟆 . 貎朱Μ 螻 螳 れ螻 螳.

  • CPU : 96622
  • Reads : 542468
  • Duration: 28173

'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

  • CPU : 1734
  • Reads: 60056
  • ation: 1766

'mmInoutItem' 企. れ  173, 朱Μ 所鍵  852, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.
'mmInoutHeader' 企. れ  1, 朱Μ 所鍵  30691, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.
'WhMaster' 企. れ  3, 朱Μ 所鍵  9, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.

れ襦 覈 豌蟇伎 豌襴 覿蟲螻 貎朱Μ 1覦 50襷蟇 襯 豌襴朱 豌襴襯 螻 蟆 覓語.

2 / 觜蟲襭 #

蠍一 覃豬. 襷螳 褐 螻螳れ 覩語..

/ 觜蟲CPUReads
9662254246828173
1734600561766
56覦 9覦 15覦