酷应用

耗时3天，上亿数据如何做到秒级查询？

百家作者：51CTO技术栈 2020-08-14 21:05:23

“

最近在忙着优化集团公司的一个报表。优化完成后，报表查询速度由从半小时以上（甚至查不出）到秒查的质变。

图片来自 Pexels

从修改 SQL 查询语句逻辑到决定创建存储过程实现，花了我 3 天多的时间，在此总结一下，希望对朋友们有帮助。

数据背景

首先项目是西门子中国在我司实施部署的 MES 项目，由于项目是在产线上运作(3 years+)，数据累积很大。

在项目的数据库中，大概上亿条数据的表有 5 个以上，千万级数据的表 10 个以上，百万级数据的表，很多…

（历史问题，当初实施无人监管，无人监控数据库这块的性能问题。PS：我刚入职不久…）

不多说，直接贴西门子中国的开发人员在我司开发的 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是一个嵌套两层的视图(出于保密性，实际名称可能不同)，里面有一张上亿数据的表和几张千万级数据的表做左连接查询
--Table1是一个数据记录超过1500万的表

这个查询语句，实际上通过我的检测和调查，在 B/S 系统前端已无法查出结果，半小时，一小时...

因为我直接在 SQL 查询分析器查，半小时都没有结果。（原因是里面对一张上亿级数据表和 3 张千万级数据表做全表扫描查询）

不由感慨，西门子中国的素质（或者说责任感）就这样？下面说说我的分析和走的弯路（思维误区），希望对你也有警醒。

探索和误区

首先相关表的索引，没有建全的，把索引给建上。

索引这步完成后，发现情况还是一样，查询速度几乎没有改善。后来想起相关千万级数据以上的表，都还没有建立表分区。于是考虑建立表分区以及数据复制的方案。

这里有必要说明下：我司报表用的是一个专门的数据库服务器，数据从产线订阅而来。就是常说的“读写分离”。

如果直接在原表上建立表分区，你会发现执行表分区的事物会直接死锁。原因是：表分区操作本身会锁表，产线还在推数据过来，这样很容易“阻塞”，“死锁”。

我想好的方案是：建立一个新表（空表），在新表上建好表分区，然后复制数据过来。

正打算这么干。等等！我好像进入了一个严重的误区！

分析：原 SQL 语句和业务需求，是对产线的数据做产品以及序列号的追溯，关键是查询条件里没有有规律的”条件”（如日期、编号），贸然做了表分区，在这里几乎没有意义！反而会降低查询性能！

好险！还是一步一步来，先做 SQL 语句分析。

对原 SQL 语句的分析

对原 SQL 语句的分析如下：

查询语句的 where 条件，有大量 @var in … or (@var =”) 的片段。
where 条件有 like ‘%’+@var+’%’。
where 条件有 case … end 函数。
多次连接同一表查询，另外使用本身已嵌套的视图表，是不是必须，是否可替代？
SQL 语句有号，视图中也有号出现。

优化设计

首先是用存储过程改写，好处是设计灵活。

核心思想是：用一个或多个查询条件（查询条件要求至少输入一个）得到临时表，每个查询条件如果查到集合，就更新这张临时表，最后汇总的时候，只需判断这个临时表是否有值。

以此类推，可以建立多个临时表，将查询条件汇总。

这样做目前来看至少两点好处：

省去了对变量进行 =@var or (@var=”)的判断。
抛弃 SQL 拼接，提高代码可读性。

再有就是在书写存储过程，这个过程中要注意：

尽量想办法使用临时表扫描替代全表扫描。
抛弃 in 和 not in 语句，使用 exists 和 not exists 替代。
和客户确认，模糊查询是否有必要，如没有必要，去掉 like 语句。
注意建立适当的，符合场景的索引。
踩死 “*” 号。
避免在 where 条件中对字段进行函数操作。
对实时性要求不高的报表，允许脏读（with(nolock)）。

存储过程

如果想参考优化设计片段的详细内容，请参阅 SQL 代码：

/**
?*?某某跟踪报表
?**/
--exec?spName1?'','','','','','','公司代号'
CREATE?Procedure?spName1
???@MESOrderID?nvarchar(320),?--工单号,最多30个
???@LotName?nvarchar(700),????--产品序列号,最多50个
???@DateCode?nvarchar(500),???--供应商批次号,最多30个
???@BatchID?nvarchar(700),????--组装件序列号/物料批号,最多50个
???@comdef?nvarchar(700),?????--组装件物料编码,最多30个
???@SNCust?nvarchar(1600),????--外部序列号,最多50个
???@OnPlant?nvarchar(20)??????--平台
AS
BEGIN
????SET?NOCOUNT?ON;??
????/**
?????*?1)定义全局的临时表，先根据六个查询条件的任意一个，得出临时表结果
?????**/
????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)类似[先根据外部序列号求解序列号,再照搬第一个判断变量的方式]
????????????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对应的查询字段
????????????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集合求解对应的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对应的查询字段
????????????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对应的查询字段
????????????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集合求解对应的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对应的查询字段
????????????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对应的查询字段
????????????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集合求解对应的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)条件判断结束
?????**/
????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