成人网站免费视频,色五月综合激情,久久久国产精品黄毛片,美女黄在线看免费日本,日韩无码电影网,国产伦精品一区二区三区四区视频 ,欧美成人在线三级免费,三级在线视频网站

? 來源網(wǎng)絡(luò)

最近在忙著優(yōu)化集團(tuán)公司的一個報表。優(yōu)化完成后，報表查詢速度有從半小時以上(甚至查不出)到秒查的質(zhì)變。從修改SQL查詢語句邏輯到?jīng)Q定創(chuàng)建存儲過程實(shí)現(xiàn)，花了我3天多的時間，在此總結(jié)一下，希望對朋友們有幫助。

數(shù)據(jù)背景

首先項(xiàng)目是西門子中國在我司實(shí)施部署的MES項(xiàng)目，由于項(xiàng)目是在產(chǎn)線上運(yùn)作(3 years+)，數(shù)據(jù)累積很大。在項(xiàng)目的數(shù)據(jù)庫中，大概上億條數(shù)據(jù)的表有5個以上，千萬級數(shù)據(jù)的表10個以上，百萬級數(shù)據(jù)的表，很多…

(歷史問題，當(dāng)初實(shí)施無人監(jiān)管，無人監(jiān)控數(shù)據(jù)庫這塊的性能問題。ps:我剛?cè)肼毑痪谩?

不多說，直接貼西門子中國的開發(fā)人員在我司開發(fā)的SSRS報表中的SQL語句：

select?distinct?b.MaterialID?as?matl_def_id, c.Descript, case?when?right(b.MESOrderID, 12) < '001000000000'?then right(b.MESOrderID, 9)
else right(b.MESOrderID, 12) end as pom_order_id, a.LotName, a.SourceLotName as ComLot,
e.DefID as ComMaterials, e.Descript as ComMatDes, d.VendorID, d.DateCode,d.SNNote, b.OnPlantID,a.SNCUST
from
(
????select m.lotname, m.sourcelotname, m.opetypeid, m.OperationDate,n.SNCUST from View1 m
????left join co_sn_link_customer as n on n.SNMes=m.LotName
????where
????( m.LotName in (select val from fn_String_To_Table(@sn,',',1)) or (@sn) = '') and
????( m.sourcelotname in (select val from fn_String_To_Table(@BatchID,',',1)) or (@BatchID) = '')
????and (n.SNCust like '%'+ @SN_ext?+ '%'?or (@SN_ext)='')
) a
left join
(
????select * from Table1 where SNType = 'IntSN'
????and SNRuleName = 'ProductSNRule'
????and OnPlantID=@OnPlant
) b on b.SN = a.LotName
inner join MMdefinitions as c on c.DefID = b.MaterialID
left join Table1 as d on d.SN = a.SourceLotName
inner join MMDefinitions as e on e.DefID = d.MaterialID
where not?exists (
?select distinct LotName, SourceLotName from ELCV_ASSEMBLE_OPS
where LotName = a.SourceLotName and SourceLotName = a.LotName
)
and?(d.DateCode in (select val from fn_String_To_Table(@DCode,',',1)) or (@DCode) = '')
and?(d.SNNote like '%'+@SNNote+'%'?or (@SNNote) = '')
and?((case when right(b.MESOrderID, 12) < '001000000000'?then right(b.MESOrderID, 9)
else right(b.MESOrderID, 12) end) in (select val from fn_String_To_Table(@order_id,',',1)) or (@order_id) = '')
and?(e.DefID in (select val from fn_String_To_Table(@comdef,',',1)) or (@comdef) = '')
--View1是一個嵌套兩層的視圖(出于保密性，實(shí)際名稱可能不同)，里面有一張上億數(shù)據(jù)的表和幾張千萬級數(shù)據(jù)的表做左連接查詢
--Table1是一個數(shù)據(jù)記錄超過1500萬的表

這個查詢語句，實(shí)際上通過我的檢測和調(diào)查，在B/S系統(tǒng)前端已無法查出結(jié)果，半小時，一小時 … 。因?yàn)槲抑苯釉赟QL查詢分析器查，半小時都沒有結(jié)果。

(原因是里面對一張上億級數(shù)據(jù)表和3張千萬級數(shù)據(jù)表做全表掃描查詢)

不由感慨，西門子中國的素質(zhì)(或者說責(zé)任感)就這樣？

下面說說我的分析和走的彎路(思維誤區(qū))，希望對你也有警醒。

探索和誤區(qū)

首先相關(guān)表的索引，沒有建全的，把索引給建上。

索引這步完成后，發(fā)現(xiàn)情況還是一樣，查詢速度幾乎沒有改善。后來想起相關(guān)千萬級數(shù)據(jù)以上的表，都還沒有建立表分區(qū)。于是考慮建立表分區(qū)以及數(shù)據(jù)復(fù)制的方案。

這里有必要說明下：我司報表用的是一個專門的數(shù)據(jù)庫服務(wù)器，數(shù)據(jù)從產(chǎn)線訂閱而來。就是常說的“讀寫分離”。

如果直接在原表上建立表分區(qū)，你會發(fā)現(xiàn)執(zhí)行表分區(qū)的事物會直接死鎖。原因是：表分區(qū)操作本身會鎖表，產(chǎn)線還在推數(shù)據(jù)過來，這樣很容易“阻塞”，“死鎖”。

我想好的方案是：建立一個新表(空表)，在新表上建好表分區(qū)，然后復(fù)制數(shù)據(jù)過來。

正打算這么干。等等！我好像進(jìn)入了一個嚴(yán)重的誤區(qū)！

分析：原SQL語句和業(yè)務(wù)需求，是對產(chǎn)線的數(shù)據(jù)做產(chǎn)品以及序列號的追溯，關(guān)鍵是查詢條件里沒有有規(guī)律的”條件”(如日期、編號)，

貿(mào)然做了表分區(qū)，在這里幾乎沒有意義！反而會降低查詢性能！

好險！還是一步一步來，先做SQL語句分析。

一. 對原SQL語句的分析

查詢語句的where條件，有大量@var in … or (@var =”) 的片段
where條件有l(wèi)ike ‘%’+@var+’%’
where條件有 case … end 函數(shù)
多次連接同一表查詢，另外使用本身已嵌套的視圖表，是不是必須，是否可替代？
SQL語句有號，視圖中也有號出現(xiàn)

二. 優(yōu)化設(shè)計

首先是用存儲過程改寫，好處是設(shè)計靈活。

核心思想是：用一個或多個查詢條件(查詢條件要求至少輸入一個)得到臨時表，每個查詢條件如果查到集合，就更新這張臨時表，最后匯總的時候，只需判斷這個臨時表是否有值。以此類推，可以建立多個臨時表，將查詢條件匯總。

這樣做目前來看至少兩點(diǎn)好處：

省去了對變量進(jìn)行 =@var or (@var=”)的判斷；
拋棄sql拼接，提高代碼可讀性。

再有就是在書寫存儲過程，這個過程中要注意：

盡量想辦法使用臨時表掃描替代全表掃描；
拋棄in和not in語句，使用exists和not exists替代；
和客戶確認(rèn)，模糊查詢是否有必要，如沒有必要，去掉like語句；
注意建立適當(dāng)?shù)模蠄鼍暗乃饕?/span>
踩死 “*” 號；
避免在where條件中對字段進(jìn)行函數(shù)操作；
對實(shí)時性要求不高的報表，允許臟讀(with(nolock))。

三. 存儲過程

如果想?yún)⒖純?yōu)化設(shè)計片段的詳細(xì)內(nèi)容，請參閱SQL代碼：

/**
?* 某某跟蹤報表
?**/
--exec spName1 '','','','','','','公司代號'
CREATE?Procedure?spName1
???@MESOrderID nvarchar(320), --工單號,最多30個
???@LotName nvarchar(700), --產(chǎn)品序列號,最多50個
???@DateCode nvarchar(500), --供應(yīng)商批次號,最多30個
???@BatchID nvarchar(700), --組裝件序列號/物料批號,最多50個
???@comdef nvarchar(700), --組裝件物料編碼,最多30個
???@SNCust nvarchar(1600), --外部序列號,最多50個
???@OnPlant nvarchar(20) --平臺
AS
BEGIN
????SET?NOCOUNT ON;
????/**
?????* 1)定義全局的臨時表，先根據(jù)六個查詢條件的任意一個，得出臨時表結(jié)果
?????**/
????CREATE?TABLE?#FinalLotName
????(
????????LotName NVARCHAR(50), --序列號
????????SourceLotName NVARCHAR(50), --來源序列號
????????SNCust NVARCHAR(128) --外部序列號
????)
????--1.1
????IF?@LotName<>''
????BEGIN
????????SELECT?Val INTO?#WorkLot FROM?fn_String_To_Table(@LotName,',',1)
????????SELECT?LotPK,LotName INTO?#WorkLotPK FROM?MMLots WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkLot b WHERE?b.Val=MMLots.LotID)
?
????????--求SourceLotPK只能在這里求
????????SELECT?a.LotPK,a.SourceLotPK into?#WorkSourcePK FROM?MMLotOperations a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkLotPK b WHERE?b.LotPK=a.LotPK) AND?a.SourceLotPK IS?NOT?NULL
?
????????SELECT?a.LotPK,a.SourceLotPK,b.LotName INTO?#WorkSourcePK2 FROM?#WorkSourcePK a JOIN?#WorkLotPK b ON?a.LotPK=b.LotPK
?
????????INSERT?INTO?#FinalLotName SELECT?a.LotName,b.LotName AS?SourceLotName,NULL?FROM?#WorkSourcePK2 a JOIN?(SELECT?LotPK,LotName FROM?MMLots WITH(NOLOCK) ) b on?a.SourceLotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待確定
????????SELECT?a.LotName,a.SourceLotName,b.SNCust INTO?#FinalLotNameX1 FROM?#FinalLotName a LEFT?JOIN?CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON?a.LotName=b.SNMes
????????DELETE?FROM?#FinalLotName
????????INSERT?INTO?#FinalLotName SELECT?LotName,SourceLotName,SNCust FROM?#FinalLotNameX1
????END
????--1.2
????IF?@BatchID<>''
????BEGIN
????????SELECT?Val INTO?#WorkSourceLot FROM?fn_String_To_Table(@BatchID,',',1)
????????IF?EXISTS(SELECT?1?FROM?#FinalLotName)--如果@LotName也不為空
????????BEGIN
????????????SELECT?a.LotName,a.SourceLotName,a.SNCust INTO?#FinalLotNameX2 FROM?#FinalLotName a WHERE?EXISTS(SELECT?1?FROM?#WorkSourceLot b WHERE?a.SourceLotName=b.Val)
????????????DELETE?FROM?#FinalLotName
????????????INSERT?INTO?#FinalLotName SELECT?LotName,SourceLotName,SNCust FROM?#FinalLotNameX2
????????END
????????ELSE?--@LotName條件為空
????????BEGIN
????????????SELECT?LotPK AS?SourceLotPK,LotName AS?SourceLotName INTO?#2?FROM?MMLots WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkSourceLot b WHERE?b.Val=MMLots.LotID)
????????????SELECT?a.LotPK,a.SourceLotPK into?#21?FROM?MMLotOperations a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#2?b WHERE?b.SourceLotPK=a.SourceLotPK)
????????????SELECT?a.LotPK,a.SourceLotPK,b.SourceLotName INTO?#22?FROM?#21?a JOIN?#2?b ON?a.SourceLotPK=b.SourceLotPK
????????????INSERT?INTO?#FinalLotName SELECT?b.LotName,a.SourceLotName,NULL?FROM?#22?a JOIN?(SELECT?LotPK,LotName FROM?MMLots WITH(NOLOCK) ) b on?a.LotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待確定
????????????SELECT?a.LotName,a.SourceLotName,b.SNCust INTO?#FinalLotNameX21 FROM?#FinalLotName a LEFT?JOIN?CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON?a.LotName=b.SNMes
????????????DELETE?FROM?#FinalLotName
????????????INSERT?INTO?#FinalLotName SELECT?LotName,SourceLotName,SNCust FROM?#FinalLotNameX21
????????END
????END
????--1.3
????IF?@SNCust<>''
????BEGIN
????????SELECT?Val INTO?#WorkCustomSN FROM?fn_String_To_Table(@SNCust,',',1)
????????IF?EXISTS(SELECT?1?FROM?#FinalLotName)--前面兩個條件至少有一個有值
????????BEGIN
????????????SELECT?a.LotName,a.SourceLotName,a.SNCust INTO?#FinalLotNameX3 FROM?#FinalLotName a WHERE?EXISTS(SELECT?1?FROM?#WorkCustomSN b WHERE?a.SNCust=b.Val)
????????????DELETE?FROM?#FinalLotName
????????????INSERT?INTO?#FinalLotName SELECT?LotName,SourceLotName,SNCust FROM?#FinalLotNameX3
????????END
????????ELSE
????????BEGIN
????????????SELECT?a.SNMes INTO?#WorkLotX FROM?CO_SN_LINK_CUSTOMER a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkCustomSN b WHERE?a.SNCust=b.Val)
????????????-------------------以下邏輯和變量1(@LotName)類似[先根據(jù)外部序列號求解序列號,再照搬第一個判斷變量的方式]
????????????SELECT?LotPK,LotName INTO?#WorkLotPKX FROM?MMLots WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkLotX b WHERE?b.SNMes=MMLots.LotID)
?
????????????--求SourceLotPK只能在這里求
????????????SELECT?a.LotPK,a.SourceLotPK into?#WorkSourcePKX FROM?MMLotOperations a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkLotPKX b WHERE?b.LotPK=a.LotPK) AND?a.SourceLotPK IS?NOT?NULL
?
????????????SELECT?a.LotPK,a.SourceLotPK,b.LotName INTO?#WorkSourcePK2X FROM?#WorkSourcePKX a JOIN?#WorkLotPKX b ON?a.LotPK=b.LotPK
?
????????????INSERT?INTO?#FinalLotName SELECT?a.LotName,b.LotName AS?SourceLotName,NULL?FROM?#WorkSourcePK2X a JOIN?(SELECT?LotPK,LotName FROM?MMLots WITH(NOLOCK) ) b on?a.SourceLotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待確定
????????????SELECT?a.LotName,a.SourceLotName,b.SNCust INTO?#FinalLotNameX31 FROM?#FinalLotName a LEFT?JOIN?CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON?a.LotName=b.SNMes
????????????DELETE?FROM?#FinalLotName
????????????INSERT?INTO?#FinalLotName SELECT?LotName,SourceLotName,SNCust FROM?#FinalLotNameX31
????????????-----------------------
????????END
????END
?
????/**
?????* 2)定義全局的臨時表，用于替換第一個全局臨時表。
?????**/
????CREATE?TABLE?#FinalCO_SN
????(
????????SN NVARCHAR(50),
????????SourceSN NVARCHAR(50),
????????SNCust NVARCHAR(128),
????????matl_def_id NVARCHAR(50),--sn的物料ID
????????ComMaterials NVARCHAR(50), --SourceSN的物料ID
????????MESOrderID NVARCHAR(20),
????????OnPlantID NVARCHAR(20),
????????VendorID NVARCHAR(20),
????????DateCode NVARCHAR(20) ,
????????SNNote NVARCHAR(512)
????)
????--2.1
????IF?@MESOrderID<>''
????BEGIN
????????-------------------------------將MESOrderID做特殊處理-----------------------------------
????????SELECT?Val INTO?#WorkMESOrderID FROM?fn_String_To_Table(@MESOrderID,',',1)
????????IF?@OnPlant='Comba'
????????BEGIN
????????????UPDATE?#WorkMESOrderID SET?Val='C000'+Val WHERE?LEN(Val)=9
????????END
????????ELSE
????????BEGIN
????????????UPDATE?#WorkMESOrderID SET?Val='W000'+Val WHERE?LEN(Val)=9
????????END
????????SELECT?SN,MaterialID,MESOrderID,OnPlantID INTO?#WorkCO_SN1 FROM?CO_SN_GENERATION a WITH(NOLOCK)
????????WHERE?SNType='IntSN'?AND?SNRuleName = 'ProductSNRule'?AND?OnPlantID=@OnPlant
????????AND?EXISTS(SELECT?1?FROM?#WorkMESOrderID b WHERE?a.MESOrderID=b.Val)
????????------------------------------------------------------------------------------------------
????????--條件判斷(邏輯分析)開始
????????IF?EXISTS(SELECT?1?FROM?#FinalLotName)--如果前面判斷的查詢條件有值
????????BEGIN
????????????--查出SourceLotName對應(yīng)的查詢字段
????????????SELECT?a.SN AS?SourceLotName,a.VendorID,a.DateCode,a.SNNote,a.MaterialID AS?ComMaterials INTO?#SourceLotNameTable FROM?CO_SN_GENERATION a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SN=b.SourceLotName)
?
????????????INSERT?INTO?#FinalCO_SN
????????????SELECT?a.LotName,a.SourceLotName,d.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM?#FinalLotName a
????????????LEFT?JOIN?#WorkCO_SN1 b ON?a.LotName=b.SN
????????????LEFT?JOIN?#SourceLotNameTable c ON?a.SourceLotName=c.SourceLotName
????????????LEFT?JOIN?CO_SN_LINK_CUSTOMER d WITH(NOLOCK) ON?a.LotName=d.SNMes
????????END
????????ELSE
????????BEGIN
????????????--已知SN集合求解對應(yīng)的SourceSN和SNCust集合------------------------------------------
????????????SELECT?LotPK,LotName INTO?#WorkLotPK410 FROM?MMLots WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkCO_SN1 b WHERE?b.SN=MMLots.LotID)
????????????SELECT?a.LotPK,a.SourceLotPK into?#WorkSourcePK420 FROM?MMLotOperations a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkLotPK410 b WHERE?b.LotPK=a.LotPK) AND?a.SourceLotPK IS?NOT?NULL
????????????SELECT?a.LotPK,a.SourceLotPK,b.LotName INTO?#WorkSourcePK430 FROM?#WorkSourcePK420 a JOIN?#WorkLotPK410 b ON?a.LotPK=b.LotPK
????????????INSERT?INTO?#FinalLotName SELECT?a.LotName,b.LotName AS?SourceLotName,NULL?FROM?#WorkSourcePK430 a JOIN?(SELECT?LotPK,LotName FROM?MMLots WITH(NOLOCK) ) b on?a.SourceLotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待確定
?
????????????SELECT?a.LotName,a.SourceLotName,b.SNCust INTO?#FinalLotNameX440 FROM?#FinalLotName a LEFT?JOIN?CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON?a.LotName=b.SNMes
????????????DELETE?FROM?#FinalLotName
????????????INSERT?INTO?#FinalLotName SELECT?LotName,SourceLotName,SNCust FROM?#FinalLotNameX440
????????????-------------------------------------------------------------------------------------
????????????SELECT?a.SN AS?SourceLotName,a.VendorID,a.DateCode,a.SNNote,a.MaterialID AS?ComMaterials INTO?#SourceLotNameTable2 FROM?CO_SN_GENERATION a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SN=b.SourceLotName)
?
????????????INSERT?INTO?#FinalCO_SN
????????????SELECT?a.LotName,a.SourceLotName,a.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM?#FinalLotName a
????????????LEFT?JOIN?#WorkCO_SN1 b ON?a.LotName=b.SN
????????????LEFT?JOIN?#SourceLotNameTable2 c ON?a.SourceLotName=c.SourceLotName
????????END????
????END
????--2.2
????IF?@DateCode<>''
????BEGIN
????????SELECT?Val INTO?#WorkDateCode FROM?fn_String_To_Table(@DateCode,',',1)
????????--此@DataCode條件求解出來的是SourceSN
????????SELECT?SN AS?SourceSN,MaterialID AS?ComMaterials,VendorID,DateCode,SNNote INTO?#WorkSourceSNT1 FROM?CO_SN_GENERATION a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkDateCode b WHERE?a.DateCode=b.Val)
????????----------------------------------------------------------------------------------------------------
????????--條件判斷(邏輯分析)開始
????????IF?EXISTS(SELECT?1?FROM?#FinalCO_SN)--如果前面判斷的查詢條件有值
????????BEGIN
????????????SELECT?a.LotName,a.SourceLotName,a.SNCust,a.MaterialID,a.ComMaterials,a.MESOrderID,a.OnPlantID,a.VendorID,a.DateCode,a.SNNote INTO?#TMP51 FROM?#FinalCO_SN a WHERE?EXISTS?(SELECT?1?FROM?#WorkDateCode b WHERE?a.DateCode=b.Val)
????????????DELETE?FROM?#FinalCO_SN
????????????INSERT?INTO?#FinalCO_SN SELECT?LotName,SourceLotName,SNCust,MaterialID,ComMaterials,MESOrderID,OnPlantID,VendorID,DateCode,SNNote FROM?#TMP51
????????END
????????ELSE
????????BEGIN
????????????IF?EXISTS(SELECT?1?FROM?#FinalLotName)
????????????BEGIN
????????????--查出SourceLotName對應(yīng)的查詢字段
????????????SELECT?a.SourceSN,a.VendorID,a.DateCode,a.SNNote,a.ComMaterials INTO?#SourceLTX5 FROM?#WorkSourceSNT1 a WHERE?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SourceSN=b.SourceLotName)
????????????--查出SN對應(yīng)的查詢字段
????????????SELECT?SN,MaterialID,MESOrderID,OnPlantID INTO?#WorkSNT510 FROM?CO_SN_GENERATION a WITH(NOLOCK)
????????????WHERE?SNType='IntSN'?AND?SNRuleName = 'ProductSNRule'?AND?OnPlantID=@OnPlant
????????????AND?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SN=b.LotName)
?
????????????INSERT?INTO?#FinalCO_SN
????????????SELECT?a.LotName,a.SourceLotName,d.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM?#FinalLotName a
????????????LEFT?JOIN?#WorkSNT510 b ON?a.LotName=b.SN
????????????LEFT?JOIN?#WorkSourceSNT1 c ON?a.SourceLotName=c.SourceSN
????????????LEFT?JOIN?CO_SN_LINK_CUSTOMER d WITH(NOLOCK) ON?a.LotName=d.SNMes
?
????????????END
????????????ELSE
????????????BEGIN
????????????????--已知SourceSN集合求解對應(yīng)的SN和SNCust集合------------------------------------------
????????????????SELECT?LotPK AS?SourceLotPK,LotName AS?SrouceLotName INTO?#WorkLotX510 FROM?MMLots WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkSourceSNT1 b WHERE?b.SourceSN=MMLots.LotID)
????????????????SELECT?a.LotPK,a.SourceLotPK into?#WorkLotX520 FROM?MMLotOperations a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkLotX510 b WHERE?b.SourceLotPK=a.SourceLotPK)
????????????????SELECT?a.LotPK,a.SourceLotPK,b.SrouceLotName INTO?#WorkLotX530 FROM?#WorkLotX520 a JOIN?#WorkLotX510 b ON?a.SourceLotPK=b.SourceLotPK
?
????????????????INSERT?INTO?#FinalLotName SELECT?b.LotName,a.SrouceLotName,NULL?FROM?#WorkLotX530 a JOIN?(SELECT?LotPK,LotName FROM?MMLots WITH(NOLOCK) ) b on?a.LotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待確定
?
????????????????SELECT?a.LotName,a.SourceLotName,b.SNCust INTO?#WorkLotX540 FROM?#FinalLotName a LEFT?JOIN?CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON?a.LotName=b.SNMes
????????????????DELETE?FROM?#FinalLotName
????????????????INSERT?INTO?#FinalLotName SELECT?LotName,SourceLotName,SNCust FROM?#WorkLotX540
????????????????-------------------------------------------------------------------------------------
????????????????SELECT?SN,MaterialID,MESOrderID,OnPlantID INTO?#WorkLotX550 FROM?CO_SN_GENERATION a WITH(NOLOCK)
????????????????WHERE?SNType='IntSN'?AND?SNRuleName = 'ProductSNRule'?AND?OnPlantID=@OnPlant
????????????????AND?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SN=b.LotName)
?
????????????????INSERT?INTO?#FinalCO_SN
????????????????SELECT?a.LotName,a.SourceLotName,a.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM?#FinalLotName a
????????????????LEFT?JOIN?#WorkLotX550 b ON?a.LotName=b.SN
????????????????LEFT?JOIN?#WorkSourceSNT1 c ON?a.SourceLotName=c.SourceSN
????????????END
????????END
????END
????--2.3
????IF?@comdef<>''
????BEGIN
????????SELECT?Val INTO?#WorkComdef FROM?fn_String_To_Table(@comdef,',',1)
????????--此@comdef條件求解出來的是SourceSN
????????SELECT?SN AS?SourceSN,MaterialID AS?ComMaterials,VendorID,DateCode,SNNote INTO?#WorkSourceSNT16 FROM?CO_SN_GENERATION a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkComdef b WHERE?a.MaterialID=b.Val)
????????----------------------------------------------------------------------------------------------------
????????--條件判斷(邏輯分析)開始
????????IF?EXISTS(SELECT?1?FROM?#FinalCO_SN)--如果前面判斷的查詢條件有值
????????BEGIN
????????????SELECT?a.LotName,a.SourceLotName,a.SNCust,a.MaterialID,a.ComMaterials,a.MESOrderID,a.OnPlantID,a.VendorID,a.DateCode,a.SNNote INTO?#TMP516 FROM?#FinalCO_SN a WHERE?EXISTS?(SELECT?1?FROM?#WorkComdef b WHERE?a.matl_def_id=b.Val)
????????????DELETE?FROM?#FinalCO_SN
????????????INSERT?INTO?#FinalCO_SN SELECT?LotName,SourceLotName,SNCust,MaterialID,ComMaterials,MESOrderID,OnPlantID,VendorID,DateCode,SNNote FROM?#TMP516
????????END
????????ELSE
????????BEGIN
????????????IF?EXISTS(SELECT?1?FROM?#FinalLotName)
????????????BEGIN
????????????--查出SourceLotName對應(yīng)的查詢字段
????????????SELECT?a.SourceSN,a.VendorID,a.DateCode,a.SNNote,a.ComMaterials INTO?#SourceLTX56 FROM?#WorkSourceSNT16 a WHERE?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SourceSN=b.SourceLotName)
????????????--查出SN對應(yīng)的查詢字段
????????????SELECT?SN,MaterialID,MESOrderID,OnPlantID INTO?#WorkSNT5106 FROM?CO_SN_GENERATION a WITH(NOLOCK)
????????????WHERE?SNType='IntSN'?AND?SNRuleName = 'ProductSNRule'?AND?OnPlantID=@OnPlant
????????????AND?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SN=b.LotName)
?
????????????INSERT?INTO?#FinalCO_SN
????????????SELECT?a.LotName,a.SourceLotName,d.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM?#FinalLotName a
????????????LEFT?JOIN?#WorkSNT5106 b ON?a.LotName=b.SN
????????????LEFT?JOIN?#WorkSourceSNT16 c ON?a.SourceLotName=c.SourceSN
????????????LEFT?JOIN?CO_SN_LINK_CUSTOMER d WITH(NOLOCK) ON?a.LotName=d.SNMes
?
????????????END
????????????ELSE
????????????BEGIN
????????????????--已知SourceSN集合求解對應(yīng)的SN和SNCust集合------------------------------------------
????????????????SELECT?LotPK AS?SourceLotPK,LotName AS?SrouceLotName INTO?#WorkLotX5106 FROM?MMLots WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkSourceSNT16 b WHERE?b.SourceSN=MMLots.LotID)
????????????????SELECT?a.LotPK,a.SourceLotPK into?#WorkLotX5206 FROM?MMLotOperations a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#WorkLotX5106 b WHERE?b.SourceLotPK=a.SourceLotPK)
????????????????SELECT?a.LotPK,a.SourceLotPK,b.SrouceLotName INTO?#WorkLotX5306 FROM?#WorkLotX5206 a JOIN?#WorkLotX5106 b ON?a.SourceLotPK=b.SourceLotPK
?
????????????????INSERT?INTO?#FinalLotName SELECT?b.LotName,a.SrouceLotName,NULL?FROM?#WorkLotX5306 a JOIN?(SELECT?LotPK,LotName FROM?MMLots WITH(NOLOCK) ) b on?a.LotPK=b.LotPK --b的里面加不加WHERE RowDeleted=0待確定
?
????????????????SELECT?a.LotName,a.SourceLotName,b.SNCust INTO?#WorkLotX5406 FROM?#FinalLotName a LEFT?JOIN?CO_SN_LINK_CUSTOMER b WITH(NOLOCK) ON?a.LotName=b.SNMes
????????????????DELETE?FROM?#FinalLotName
????????????????INSERT?INTO?#FinalLotName SELECT?LotName,SourceLotName,SNCust FROM?#WorkLotX5406
????????????????-------------------------------------------------------------------------------------
????????????????SELECT?SN,MaterialID,MESOrderID,OnPlantID INTO?#WorkLotX5506 FROM?CO_SN_GENERATION a WITH(NOLOCK)
????????????????WHERE?SNType='IntSN'?AND?SNRuleName = 'ProductSNRule'?AND?OnPlantID=@OnPlant
????????????????AND?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SN=b.LotName)
?
????????????????INSERT?INTO?#FinalCO_SN
????????????????SELECT?a.LotName,a.SourceLotName,a.SNCust,b.MaterialID,c.ComMaterials,b.MESOrderID,b.OnPlantID,c.VendorID,c.DateCode,c.SNNote FROM?#FinalLotName a
????????????????LEFT?JOIN?#WorkLotX5506 b ON?a.LotName=b.SN
????????????????LEFT?JOIN?#WorkSourceSNT16 c ON?a.SourceLotName=c.SourceSN
????????????END
????????END
????END
?
????/**
?????* 3)條件判斷結(jié)束
?????**/
????IF?EXISTS(SELECT?1?FROM?#FinalLotName)
????BEGIN
????????IF?EXISTS(SELECT?1?FROM?#FinalCO_SN)
????????BEGIN--3.1
????????????SELECT?a.matl_def_id,b.Descript,a.MESOrderID AS?pom_order_id,a.SN AS?LotName,a.SourceSN AS?ComLot,
???????????????????a.ComMaterials,c.Descript AS?ComMatDes,a.VendorID,a.DateCode,a.SNNote,
???????????????????OnPlantID,SNCust FROM?#FinalCO_SN a
???????????????????JOIN?MMDefinitions b WITH(NOLOCK) ON?a.matl_def_id=b.DefID
???????????????????JOIN?MMDefinitions c WITH(NOLOCK) ON?a.ComMaterials=c.DefID
????????????WHERE?NOT?EXISTS(select?distinct?SN, SourceSN from?#FinalCO_SN x
?????????????????????????????where?x.SN = a.SourceSN and?x.SourceSN = a.SN)
????????END
????????ELSE
????????BEGIN--3.2
????????????--3.2.1求解SN的必查字段
????????????SELECT?SN,MaterialID,MESOrderID,OnPlantID INTO?#FinalSNX1 FROM?CO_SN_GENERATION a WITH(NOLOCK)
????????????WHERE?SNType='IntSN'?AND?SNRuleName = 'ProductSNRule'?AND?OnPlantID=@OnPlant
????????????AND?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SN=b.LotName)
????????????--3.2.2求解SourceSN的必查字段
????????????SELECT?a.SN AS?SourceLotName,a.VendorID,a.DateCode,a.SNNote,a.MaterialID AS?ComMaterials INTO?#FinalSNX2 FROM?CO_SN_GENERATION a WITH(NOLOCK) WHERE?EXISTS(SELECT?1?FROM?#FinalLotName b WHERE?a.SN=b.SourceLotName)
?
????????????SELECT?b.MaterialID AS?matl_def_id,x.Descript,b.MESOrderID AS?pom_order_id,b.SN AS?LotName,c.SourceLotName AS?ComLot,c.ComMaterials,y.Descript AS?ComMatDes,c.VendorID,c.DateCode,c.SNNote,b.OnPlantID,a.SNCust
????????????FROM?#FinalLotName a
????????????LEFT?JOIN?#FinalSNX1 b ON?a.LotName=b.SN
????????????LEFT?JOIN?#FinalSNX2 c ON?a.SourceLotName=c.SourceLotName
????????????JOIN?MMDefinitions x WITH(NOLOCK) ON?b.MaterialID=x.DefID
????????????JOIN?MMDefinitions y WITH(NOLOCK) ON?c.ComMaterials=y.DefID
????????????WHERE?NOT?EXISTS(
????????????????SELECT?DISTINCT?* FROM?#FinalLotName z
????????????????WHERE?z.LotName=a.SourceLotName and?z.SourceLotName=a.LotName
????????????)
????????END
????END
????ELSE
????BEGIN
????????IF?EXISTS(SELECT?1?FROM?#FinalCO_SN)
????????BEGIN--3.3
????????????SELECT?a.matl_def_id,b.Descript,a.MESOrderID AS?pom_order_id,a.SN AS?LotName,a.SourceSN AS?ComLot,
???????????????????a.ComMaterials,c.Descript AS?ComMatDes,a.VendorID,a.DateCode,a.SNNote,
???????????????????OnPlantID,SNCust FROM?#FinalCO_SN a
???????????????????JOIN?MMDefinitions b WITH(NOLOCK) ON?a.matl_def_id=b.DefID
???????????????????JOIN?MMDefinitions c WITH(NOLOCK) ON?a.ComMaterials=c.DefID
????????????WHERE?NOT?EXISTS(select?distinct?SN, SourceSN from?#FinalCO_SN x
?????????????????????????????where?x.SN = a.SourceSN and?x.SourceSN = a.SN)
????????END
????????ELSE
????????BEGIN--3.4
????????????PRINT 'There is no queryable condition,please enter at less a query conditon.'
????????END
????END
END
GO