较深的存储过程

最新推荐文章于 2024-07-22 09:40:00 发布
gaogyj1688
最新推荐文章于 2024-07-22 09:40:00 发布
阅读量297
点赞数
分类专栏：存储过程文章标签：存储 class sql output join table
本文链接：https://blog.csdn.net/gaogyj1688/article/details/2538135
版权
存储过程专栏收录该内容
0 篇文章 0 订阅
订阅专栏
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO



ALTER PROCEDURE [dbo].[Pro_WaterData_Category]

@WATERCODE int,--水源代码

--@RCODE  varchar(20),--河流代码字符串   

--@RSCODE varchar(100),--断面代码字符串

@itemStr varchar(500),--指标字符串

@frequency varchar(10),--数据类别

@lQssj int,--起始时间

@lZzsj int, --终止时间

@cxlb int, --查询类别 0单独分析 1同期对比  2连续对比

@sjlb int --时间类别 0年 1半年 2季度 3月



as

begin



DECLARE @tj nvarchar(300) --where条件

DECLARE @L_SQL nVARCHAR(4000) --在取中英文指标时变量

DECLARE @itemYw nVarchar(20)--指标英文名

DECLARE @itemZw nvarchar(20)--指标中文名

DECLARE @zcode varchar(20) --测站代码

DECLARE @zname varchar(20) --测站名称

DECLARE @cycode varchar(20) --采样点代码

DECLARE @cyname varchar(20) --采样点名称

DECLARE @lwatercode varchar(20) --饮用水代码

DECLARE @lwatername varchar(20) --饮用水名称





DECLARE @sql nVARCHAR(4000) 

DECLARE @item VARCHAR(20) --指标名称

DECLARE @tjyear nvarchar(300) --条件2

DECLARE @tjlbadd int --条件3

DECLARE @tjcity int --城市

DECLARE @tjlbtype nvarchar(300)  --条件4

DECLARE @tjlbtypes nvarchar(300)  --条件5

DECLARE @fyy nvarchar(20) --富营养列

DECLARE @fyyVal decimal(18,5) --富营养值

DECLARE @avgsjVal decimal(8,2) --平均值

DECLARE @sjval decimal(8,2) --实际值

DECLARE @lrcode varchar(20) --河流代码

DECLARE @lrscode varchar(20) --断面代码

DECLARE @lrsname varchar(20) --断面名称

DECLARE @lstcode  varchar(20) --地区代码

DECLARE @rise smallint  

DECLARE @class1 decimal(18,5)

DECLARE @class2 decimal(18,5)

DECLARE @class3 decimal(18,5) 

DECLARE @class4 decimal(18,5)

DECLARE @class5 decimal(18,5)  

DECLARE @waterLb varchar(10)  --水质类别

DECLARE @wrwzs  decimal(8,2) --污染物指数

DECLARE @wrjb  varchar(20) --污染级别

DECLARE @sumwrwzs decimal(8,2) --污染物指数总和

DECLARE @avgwrwzs decimal(8,2) --污染物指数平均值

DECLARE @zhyyzs   decimal(8,2) --综合营养指数

DECLARE @COUNT  int  --计数

DECLARE @dlxm  varchar(100)  --定类项目

DECLARE @grpBy  varchar(150) --group by的时间条件 

DECLARE @sjzh varchar(150) --时间组合变量







set @tj=''

--  表T_Bas_WaterData  水源代码

if @WATERCODE=0

	begin

		SET @tj=@tj+' 1=1'

	end

else 

	begin

		SET @tj=@tj+' a.whcode='+Convert(nvarchar,@WATERCODE)

	end



-- 表T_Bas_WaterData 数据类别

if @frequency=''

	begin

		 set @tj= @tj+' and 1=1'

	end 

else

	begin

		SET @tj=@tj+' and a.frequency='''+@frequency+''''

	end



--表T_Bas_WaterData 按查询类别定义起止时间

if @cxlb=0

	begin

		set @tj=@tj+' and (((a.ye*100+a.mon)*100+a.day)>='+CONVERT(NVARCHAR,@lQssj) + ' and ((a.ye*100+a.mon)*100+a.day)<='+CONVERT(NVARCHAR,@lZzsj)+')'

    end

else if @cxlb=1

	begin

		set @tj=@tj+' and ((((a.ye*100+a.mon)*100+a.day)>='+CONVERT(CHAR,@lQssj) + ' and ((a.ye*100+a.mon)*100+a.day)<='+CONVERT(NVARCHAR,@lZzsj)+')'

		set @tj=@tj+ ' or  (((a.ye*100+a.mon)*100+a.day)>='+CONVERT(CHAR,@lQssj-10000) + ' and ((a.ye*100+a.mon)*100+a.day)<='+CONVERT(NVARCHAR,@lZzsj-10000)+'))'

	end

else

	begin

		set @tj=@tj+' and (((a.ye*100+a.mon)*100+a.day)>='+CONVERT(CHAR,@lQssj) + ' and ((a.ye*100+a.mon)*100+a.day)<='+CONVERT(NVARCHAR,@lZzsj)+')'

	end

--定义游标，循环取选择的指标项

set @sql=''

set @sql='DECLARE  C_Item CURSOR FOR select item from f_splitSTR('''+@itemStr+''','','')'

exec (@sql)



open C_Item;--打开游标

fetch NEXT FROM C_Item INTO @item; --循环游标



--删除临时表,先判断是否已经存在,如果存在则删除

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tempDmLbData') and type='U')

begin  

	drop table #tempDmLbData;

end

--创建临时表

CREATE table #tempDmLbData

(    

    lSPCODE varchar(20),--采样点代码

	lSPNAME varchar(20),---采样点名称



    lwatercode varchar(20), --饮用水源地代码

	lwatername varchar(20),---饮用水源地名称



    stcode varchar(20), --(测站)地区代码 

    stname varchar(20), --(测站) 地区名称

    RCODE varchar(20), --河流代码

    RNAME varchar(20), --河流名称

    rscode int, --断面代码

    rsname varchar(12), --断面名称



    frequency varchar(20),---数据类别

    item varchar(20),---指标项英文

    itemname varchar(20),---指标项中文

    ye int,

    sxyear varchar(10),

    quarte int,

    mon int,

    da int,

    sjVal decimal(8,2),

    rise smallint,

    class1 decimal(18,5),

    class2 decimal(18,5),

    class3 decimal(18,5),

    class4 decimal(18,5),

    class5 decimal(18,5)

);



--如果存在先删除临时表

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#t_temp2') and type='U')

begin  

	drop table #t_temp2;

end

CREATE TABLE #t_temp2

(

 Cbshu int,

  lwatercode varchar(20), --饮用水源地代码

  lwatername varchar(20),---饮用水源地名称

  item varchar(20),---指标项英文

  itemname varchar(20),---指标项中文





    stcode varchar(20), --(测站)地区代码 

    stname varchar(20), --(测站) 地区名称



    lSPCODE varchar(20),--采样点代码

	lSPNAME varchar(20),---采样点名称

-- RCODE varchar(20), --河流代码

-- RNAME varchar(20), --河流名称 

-- rscode int,

-- rsname varchar(12),





 avgsjVal  decimal(8,2),

 waterLb varchar(10),

 wrwzs decimal(8,2),

 sumwrwzs decimal(8,2),

 avgwrwzs decimal(8,2),

 wrjb  varchar(20),

 zhyyzs decimal(8,2),

 szhyyzs decimal(8,2),

 dlxm  nvarchar(200),

 icount int,

 ye int,

 sxyear varchar(10),

 quart int,

 mon int ,

 sjzh varchar(50)

);





WHILE @@FETCH_STATUS = 0

BEGIN

  --取指标英文名

  set @L_SQL=''

  set @L_SQL = 'select @itemyw=substring('''+@item+''',1,CHARINDEX(''+'','''+@item+''')-1)'

  exec  sp_executesql @L_SQL,N'@itemyw varchar(20) output',@itemyw output

  --取指标中文名

  set @L_SQL=''

  set @L_SQL ='select @itemzw=substring('''+@item+''',CHARINDEX(''+'','''+@item+''')+1,len('''+@item+'''))'

  exec  sp_executesql @L_SQL,N'@itemzw varchar(20) output',@itemzw output



  --往表中插入数据(采样点代码、采样点名称、测站代码、测站名称、饮用水代码、饮用水名称、数据分类、指标英文名称、指标中文名称、年、月、日、实际值、判断是大于还是小于、1类到5类)

  set @L_SQL = ''

  SET @L_SQL='INSERT INTO #tempDmLbData (lSPCODE,lSPNAME,stcode,stname,lwatercode,lwatername,frequency,item,itemname,ye,mon,da,sjVal,Rise,Class1,Class2,Class3,Class4,Class5)'

  set @L_SQL=@L_SQL+' select a.SPCODE,c.SPNAME,a.STCODE,d.stname,a.whcode,b.WHNAME,a.frequency,'''+@itemyw+''' item,'''+@itemzw+''' itemname,a.ye,a.mon,a.day,'+isnull(@itemyw,0) +',e.rise,e.class1,e.class2,e.class3,e.class4,e.class5' 

  set @L_SQL=@L_SQL+' from  T_Bas_WaterData a left join T_Cod_Water b on a.stcode =b.stcode  and a.ye=b.ye and b.whcode=a.whcode '

  set @L_SQL=@L_SQL+' left join T_Bas_WaterSample c on a.stcode=c.stcode and  a.ye=c.ye and c.spcode=a.spcode '

  set @L_SQL=@L_SQL+' left join T_Bas_StIndex d on a.stcode=d.stcode and a.ye=d.ye' 

  set @L_SQL=@L_SQL+' left join T_Bas_GB3838_2002 e on e.item='''+@itemyw+''''

  set @L_SQL=@L_SQL+' where '+ @tj

  print @L_SQL

  exec (@L_SQL)--执行符合条件的SQL



  fetch NEXT FROM C_Item INTO @item;---循环游标

END;



CLOSE  C_Item;

DEALLOCATE C_Item;---游标结束





--修改季，上半年，下半年

update #tempDmLbData set quarte = case when mon in(1,2,3) then 1 when mon in (4,5,6) then 2

     when mon in(7,8,9) then 3 when mon in(10,11,12) then 4 end,sxyear=case when mon in(1,2,3,4,5,6) then '上半年' when mon in(7,8,9,10,11,12) then '下半年' end ;



--按时间类别来取时间组合

if @sjlb=0 

begin

  set @grpBy='convert(nvarchar(4),ye)+''年'''

end

if @sjlb=1

begin

  set @grpBy='convert(nvarchar(4),ye)+sxyear'

end

if @sjlb=2 

begin

  set @grpBy='convert(nvarchar(4),ye)+''年''+convert(nvarchar(4),quarte)+''季度'''

end

if @sjlb=3 

begin

  set @grpBy='convert(nvarchar(4),ye)+''年''+right(''0''+convert(nvarchar(4),mon),2)+''月'''

end



--定义游标C_Val 

set @itemYw=''

set @itemZw=''

--查询：  时间类别、测站代码、测站名字、采样点代码、采样点名称、饮用水代码、饮用水名称、指标英文、指标中文、判断符、类别1到5、实际值

set @sql='DECLARE  C_Val CURSOR FOR select '+@grpBy+',stcode,stname,lSPCODE,lSPNAME,lwatercode,lwatername,item,itemname,rise,class1,class2,class3,class4,class5, avg(sjval) avgsj from #tempDmLbData '

set @sql=@sql+'group by '+@grpBy+',stcode,stname,lSPCODE,lSPNAME,lwatercode,lwatername,item,itemname,rise,class1,class2,class3,class4,class5' 

exec (@sql)



--------------------

set @tjyear='' 

set @lstcode=''

set @tjlbadd =0

--

---------

 open C_Val;

-- 时间类别、测站代码、测站名字、采样点代码、采样点名称、饮用水代码、饮用水名称、指标英文、指标中文、判断符、类别1到5、实际值

fetch NEXT FROM C_Val INTO @sjzh,@zcode,@zname,@cycode,@cyname,@lwatercode,@lwatername,@itemYw,@itemZw,@rise,@class1,@class2,@class3,@class4,@class5,@avgsjVal;

WHILE @@FETCH_STATUS = 0

BEGIN



 --水质类别、在这里判断是大于还是小于

 if @rise=-1 

    begin

		if @avgsjVal>=@class1 

         begin

			set @waterLb = 'I'							

         end



		else if @avgsjVal>=@class2 

          begin

		  set @waterLb = 'II'

          end

		else if @avgsjVal>=@class3

         begin 

		 set @waterLb = 'III'

         end

		else if @avgsjVal>=@class4 

			begin

				set @waterLb = 'IV'

			end

    	else if @avgsjVal>=@class5 

         begin

		 set @waterLb = 'V'

         end

		else

        begin 

		set @waterLb = '劣V'

        end

   end

 else

 begin

   if @avgsjVal<=@class1 

         begin

		 set @waterLb = 'I'

         end

		else if @avgsjVal<=@class2 

          begin

		  set @waterLb = 'II'

          end

		else if @avgsjVal<=@class3

         begin 

		 set @waterLb = 'III'

         end

		else if @avgsjVal<=@class4 

         begin

		 set @waterLb = 'IV'

         end

		else if @avgsjVal<=@class5 

         begin

		 set @waterLb = 'V'

         end

		else

        begin 

		set @waterLb = '劣V'

		end

 end



 --插入临时表

---- 时间类别、测站代码、测站名字、采样点代码、采样点名称、饮用水代码、饮用水名称、指标英文、指标中文、实际值、类别

   insert into #t_temp2(sjzh,stcode,stname,lSPCODE,lSPNAME,lwatercode,lwatername,item,itemname,avgsjVal,waterLb)

   select @sjzh,@zcode,@zname,@cycode,@cyname,@lwatercode,@lwatername,@itemYw,@itemZw,@avgsjVal,@waterLb;



-- 时间类别、测站代码、测站名字、采样点代码、采样点名称、饮用水代码、饮用水名称、指标英文、指标中文、判断符、类别1到5、实际值

fetch NEXT FROM C_Val INTO @sjzh,@zcode,@zname,@cycode,@cyname,@lwatercode,@lwatername,@itemYw,@itemZw,@rise,@class1,@class2,@class3,@class4,@class5,@avgsjVal;

END;

CLOSE  C_Val;

DEALLOCATE C_Val;







--以下得到数据集

--select * from #t_temp2 

--select * from #tempDmLbData

--按一个条件时间分组算类别总个数

--select sjzh 时间组合,Convert(decimal(8,2),count(waterLb)) 类别总和个数 from #t_temp2 group by sjzh

-------按时间组合、(没有采样点)测站代码、测站名称、类别分组得到采样点个数

--select sjzh 时间组合,stcode 测站代码,stname 测站名称,count(lSPNAME) 采样点名称个数,waterLb 指标类别 from #t_temp2 group by sjzh,stcode,stname,waterLb







--以下是第一个大的组合。得到超标率++++++

select a.时间组合,(case a.测站代码 when a.测站代码 then (select top 1 cc.StName from T_Bas_StIndex cc where cc.STCODE=a.测站代码) end) 测站代码,'超标率' as 数据类别,Convert(decimal(8,2),(a.超标总个数/b.总个数)) 数值 from(

---------按时间组合、测站代码、采样代码分组，当类别大于等于三类得到采样点个数总和(超标总个数)，这里包了一层

select a.时间组合,a.测站代码,Convert(decimal(8,2),count(a.采样代码)) 超标总个数 from (

select sjzh 时间组合,stcode 测站代码,lSPCODE 采样代码 from #t_temp2 where waterLb>='III' group by sjzh,stcode,lSPCODE

) a  group by a.时间组合,a.测站代码



) a,(

---------按时间组合、测站代码、采样代码分组，当类别大于等于三类得到采样点个数总和(超标总个数和达标总个数)，这里包了一层

select a.时间组合,a.测站代码,Convert(decimal(8,2),count(a.采样代码)) 总个数 from (

select sjzh 时间组合,stcode 测站代码,lSPCODE 采样代码 from #t_temp2  group by sjzh,stcode,lSPCODE

) a  group by a.时间组合,a.测站代码

) b where a.时间组合=b.时间组合 and a.测站代码=b.测站代码 

--上面是第一个大的组合。得到超标率++++++



union----****************联接(以下语句共实和上面语句一样，只是达标率1-超标率)



--以下是第二个大的组合。得到达标率++++++

select a.时间组合,(case a.测站代码 when a.测站代码 then (select top 1 cc.StName from T_Bas_StIndex cc where cc.STCODE=a.测站代码) end) 测站代码,'达标率' as lu,Convert(decimal(8,2),(1-a.超标总个数/b.总个数)) 达标率 from(

---------按时间组合、测站代码、采样代码分组，当类别大于等于三类得到采样点个数总和(超标总个数)，这里包了一层

select a.时间组合,a.测站代码,Convert(decimal(8,2),count(a.采样代码)) 超标总个数 from (

select sjzh 时间组合,stcode 测站代码,lSPCODE 采样代码 from #t_temp2 where waterLb>='III' group by sjzh,stcode,lSPCODE

) a  group by a.时间组合,a.测站代码



) a,(

---------按时间组合、测站代码、采样代码分组，当类别大于等于三类得到采样点个数总和(超标总个数和达标总个数)，这里包了一层

select a.时间组合,a.测站代码,Convert(decimal(8,2),count(a.采样代码)) 总个数 from (

select sjzh 时间组合,stcode 测站代码,lSPCODE 采样代码 from #t_temp2  group by sjzh,stcode,lSPCODE

) a  group by a.时间组合,a.测站代码

) b where a.时间组合=b.时间组合 and a.测站代码=b.测站代码 

--上面是第一个大的组合。得到超标率++++++



union----****************联接(以下语句共实和上面语句一样，只是达标率1-超标率)







-----下面包一层

select a.时间组合,(case a.测站代码 when a.测站代码 then (select top 1 cc.StName from T_Bas_StIndex cc where cc.STCODE=a.测站代码) end) 测站代码,a.类别 数据类别,count(a.采样代码) 数量 from (

select sjzh 时间组合,stcode 测站代码,lspcode 采样代码,max(waterLb) 类别 from  #t_temp2 group by sjzh,stcode,lspcode

) a group by  a.时间组合,a.测站代码,a.类别

----



union----****************联接(以下语句共实和上面语句一样，只是达标率1-超标率)





---------按时间组合、测站代码、采样代码分组，当类别大于等于三类得到采样点个数总和(超标总个数)，这里包了一层

select a.时间组合,(case a.测站代码 when a.测站代码 then (select top 1 cc.StName from T_Bas_StIndex cc where cc.STCODE=a.测站代码) end) 测站代码,'超标个数' 数据类别,Convert(decimal(8,2),count(a.采样代码)) 超标总个数 from (

select sjzh 时间组合,stcode 测站代码,lspcode 采样代码 from #t_temp2 where waterLb>='III' group by sjzh,stcode,lspcode

) a  group by a.时间组合,a.测站代码



end
gaogyj1688
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
较深的存储过程

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Pro_WaterData_Category]@WATERCODE int,--水源代码--@RCODE varchar(20),--河流代码字符串 --@RSCODE varchar(100),--断面代
复制链接

扫一扫