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

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





  • 13
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

JagTom

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值