sql学习：复杂的存储过程(查询昨天存在的物料近期七天的检测数据)

最新推荐文章于 2024-07-04 12:15:00 发布

JagTom

最新推荐文章于 2024-07-04 12:15:00 发布

阅读量484

点赞数 13

文章标签： sql 学习数据库

本文链接：https://blog.csdn.net/m0_46845579/article/details/135101300

版权

文章讨论了如何优化SQL查询，以高效地获取昨天送检材料的近七条数据，避免全量查询，通过创建临时表并利用Formid主键进行分组和循环插入数据，提升查询性能。

摘要由CSDN通过智能技术生成

首先，我们思考，你们现在有个需求，需求是昨天有一批材料送过来，你昨天对这批材料进行了检测，现在要获取昨天进行了检测材料的近七条数据。首先数据库中检测时间存储的是日，不是到秒，所以按照时间来倒排是不可取的，只能按照Formid主键来倒排。

然后就是要获取昨天有数据的材料编码了

--将查询到的数据存储到temp临时表中
select t.MaterialCode,t.CheckName ,t.SupplierCName,t.Company into  #temp  from(
	Select  Row_Number() Over (partition by b.MaterialCode,a.CheckName Order By a.FormNo) rowid,a.FormNo,b.MaterialCode,a.CheckName,Avg(Convert(Decimal,a.CheckData)) data, Max(B.InputDate) InputDate,b.Company,tblSupplier.SupplierCName,b.CheckDate
	From tblMaterialCheckDetail a ,tblMaterialCheckMaster b 
	LEFT OUTER JOIN tblSupplier on tblSupplier.SupplierCode =  b.SupplierCode
	Where a.FormNo=b.FormNo And ISNUMERIC(CheckData)=1 
	And b.InputDate = convert(varchar(11),dateadd(day,-1,getdate()),120) 
	Group By a.FormNo,b.MaterialCode,a.CheckName,b.Company,tblSupplier.SupplierCName,b.CheckDate,b.SupplierCode
	)t  group by t.MaterialCode,t.CheckName ,t.SupplierCName,t.Company;

原本的思路时，查询数据库中的所有物料然后按照rowid取小于等于7的，但是这是把所有数据都查出来，非常耗时，既然已经获取了昨天的物料，可以写循环，创建一张临时表，循环查询每个物料的前七条数据然后存到临时表中，再进行下面的操作


create table #temp_data(
    rowid int,
	FormNo varchar(50),  
    MaterialCode VARCHAR(50),  
    CheckName VARCHAR(50),  
    Data DECIMAL,  
    InputDate DATE,  
    Company VARCHAR(50),  
    SupplierCName VARCHAR(50),  
    CheckDate DATE  
);

	DECLARE @MaterialCode NVARCHAR(50), @CheckName NVARCHAR(50), @SupplierCName NVARCHAR(50), @Company NVARCHAR(50)
	DECLARE k CURSOR FOR SELECT MaterialCode,CheckName,SupplierCName,Company FROM #temp
	--打开游标
	open k
	--开始遍历,将下一行的数据存入两个变量中
	FETCH NEXT FROM K INTO @MaterialCode, @CheckName,@SupplierCName,@Company
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO #temp_data 
		Select top 7  Row_Number() Over (partition by b.MaterialCode,a.CheckName,b.Company,tblSupplier.SupplierCName Order By a.FormNo desc) rowid,a.FormNo,b.MaterialCode,a.CheckName,Avg(Convert(Decimal,a.CheckData)) data, Max(B.InputDate) InputDate,b.Company,tblSupplier.SupplierCName,b.CheckDate
		From tblMaterialCheckDetail a ,tblMaterialCheckMaster b 
		LEFT OUTER JOIN tblSupplier on tblSupplier.SupplierCode =  b.SupplierCode
		Where a.FormNo=b.FormNo And ISNUMERIC(CheckData)=1  AND b.MaterialCode = @MaterialCode AND a.CheckName = @CheckName  AND  SupplierCName=@SupplierCName and Company = @Company
		Group By a.FormNo,b.MaterialCode,a.CheckName,b.Company,tblSupplier.SupplierCName,b.CheckDate
		--ORDER BY a.FormNo DESC
	FETCH NEXT FROM K INTO @MaterialCode, @CheckName,@SupplierCName,@Company
	END
	CLOSE k
	DEALLOCATE k

完整SQL（仅供学习使用）


ALTER PROCEDURE [dbo].[name]
	

AS

BEGIN


select t.MaterialCode,t.CheckName ,t.SupplierCName,t.Company into  #temp  from(
	Select  Row_Number() Over (partition by b.MaterialCode,a.CheckName Order By a.FormNo) rowid,a.FormNo,b.MaterialCode,a.CheckName,Avg(Convert(Decimal,a.CheckData)) data, Max(B.InputDate) InputDate,b.Company,tblSupplier.SupplierCName,b.CheckDate
	From tblMaterialCheckDetail a ,tblMaterialCheckMaster b 
	LEFT OUTER JOIN tblSupplier on tblSupplier.SupplierCode =  b.SupplierCode
	Where a.FormNo=b.FormNo And ISNUMERIC(CheckData)=1 
	And b.InputDate = convert(varchar(11),dateadd(day,-58,getdate()),120) 
	Group By a.FormNo,b.MaterialCode,a.CheckName,b.Company,tblSupplier.SupplierCName,b.CheckDate,b.SupplierCode
	)t  group by t.MaterialCode,t.CheckName ,t.SupplierCName,t.Company;

--select * from #temp;

create table #temp_data(
    rowid int,
	FormNo varchar(50),  
    MaterialCode VARCHAR(50),  
    CheckName VARCHAR(50),  
    Data DECIMAL,  
    InputDate DATE,  
    Company VARCHAR(50),  
    SupplierCName VARCHAR(50),  
    CheckDate DATE  
);

	DECLARE @MaterialCode NVARCHAR(50), @CheckName NVARCHAR(50), @SupplierCName NVARCHAR(50), @Company NVARCHAR(50)
	DECLARE k CURSOR FOR SELECT MaterialCode,CheckName,SupplierCName,Company FROM #temp
	--打开游标
	open k
	--开始遍历,将下一行的数据存入两个变量中
	FETCH NEXT FROM K INTO @MaterialCode, @CheckName,@SupplierCName,@Company
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO #temp_data 
		Select top 7  Row_Number() Over (partition by b.MaterialCode,a.CheckName,b.Company,tblSupplier.SupplierCName Order By a.FormNo desc) rowid,a.FormNo,b.MaterialCode,a.CheckName,Avg(Convert(Decimal,a.CheckData)) data, Max(B.InputDate) InputDate,b.Company,tblSupplier.SupplierCName,b.CheckDate
		From tblMaterialCheckDetail a ,tblMaterialCheckMaster b 
		LEFT OUTER JOIN tblSupplier on tblSupplier.SupplierCode =  b.SupplierCode
		Where a.FormNo=b.FormNo And ISNUMERIC(CheckData)=1  AND b.MaterialCode = @MaterialCode AND a.CheckName = @CheckName  AND  SupplierCName=@SupplierCName and Company = @Company
		Group By a.FormNo,b.MaterialCode,a.CheckName,b.Company,tblSupplier.SupplierCName,b.CheckDate
		--ORDER BY a.FormNo DESC
	FETCH NEXT FROM K INTO @MaterialCode, @CheckName,@SupplierCName,@Company
	END
	CLOSE k
	DEALLOCATE k

	--select * from  #temp_data


	select a.Company as 会计单位,a.SupplierCName as 供应商名称,a.MaterialCode as 物料编码,M.MaterialName as 物料名称,M.MaterialModel as 型号,M.MaterialStd as 规格,M.Unit1 as 单位,
					a.CheckName as 检测项目,MCS.CheckStdParam1 as 下限,MCS.CheckStdParam2 as 上限,a.FormNo as 检测单号1,a.InputDate as 制单日期1,b.FormNo as 检测单号2,b.InputDate as 制单日期2,c.FormNo as 检测单号3,c.InputDate as 制单日期3,
					d.FormNo as 检测单号4,d.InputDate as 制单日期4,e.FormNo as 检测单号5,e.InputDate as 制单日期5,f.FormNo as 检测单号6,f.InputDate as 制单日期6,g.FormNo as 检测单号7,g.InputDate as 制单日期7,a.data as 检测数值1,b.data as 检测数值2,
					c.data as 检测数值3,d.data as 检测数值4,e.data as 检测数值5,f.data as 检测数值6,g.data as 检测数值7
	 from #temp_data a
	 inner join #temp_data b on a.rowid=b.rowid+1 And a.MaterialCode=b.MaterialCode And a.CheckName=b.CheckName And a.Company=b.Company
	 inner join #temp_data c on a.rowid=c.rowid+2 And a.MaterialCode=c.MaterialCode And a.CheckName=c.CheckName And a.Company=c.Company
	 inner join #temp_data d on a.rowid=d.rowid+3 And a.MaterialCode=d.MaterialCode And a.CheckName=d.CheckName And a.Company=d.Company
	 inner join #temp_data e on a.rowid=e.rowid+4 And a.MaterialCode=e.MaterialCode And a.CheckName=e.CheckName And a.Company=e.Company
	 inner join #temp_data f on a.rowid=f.rowid+5 And a.MaterialCode=f.MaterialCode And a.CheckName=f.CheckName And a.Company=f.Company
	 inner join #temp_data g on a.rowid=g.rowid+6 And a.MaterialCode=g.MaterialCode And a.CheckName=g.CheckName And a.Company=g.Company
	 inner join tblMaterialCheckTrack MCT on a.MaterialCode=MCT.MaterialCode And a.CheckName=MCT.CheckName And a.Company=mct.Company
	 inner join tblMaterial M on MCT.MaterialCode=M.MaterialCode
	 inner join tblMaterialCheckStd MCS on MCT.MaterialCode=MCS.MaterialCode and MCT.CheckName=MCS.CheckName
	 where MCT.TrackType='异常跟踪' and MCT.Used=1 and ((a.data>b.data And b.data>c.data And c.data>d.data And d.data>e.data And e.data>f.data And f.data>g.data And a.Rowid>=7)
	 Or (a.data<b.data And b.data<c.data And c.data<d.data And d.data<e.data AND e.data<f.data  AND f.data<g.data  And a.Rowid>=7))  

	 drop table #temp,#temp_data;
		
	
END