查询预警中的一个较为复杂的存储过程--dhg
ALTER PROCEDURE dbo.SP_QueryUnitOK
@SearchID int
AS
declare @fullcode varchar(128);
declare @AccountId int;
declare @TastID int;
declare @ReportName varchar(20);
declare @nullString char(1);
declare @ReportID int ;
declare @SpecialID int ;
declare @FieldName varchar(7999);
-- 指标
declare @IndicatorField varchar(7000);
declare @NewTable varchar(7000);
declare @InputID int;
declare @DataValue decimal(11,2);
--wher
declare @ID int;
declare @Where varchar(7999);
declare @ValueType int;
declare @ConditionType int;
declare @TemConditionType int;
declare @AndOr char(5);
declare @IndicatorId int;
declare @PropertyID int;
declare @ConFieldName varchar(256);
declare @spliti varchar(256); ----------------------------dhg
declare @index int;
declare @Op char(10);
declare @StartValue varchar(100);
declare @EndValue varchar(100);
declare @DisplayValue varchar(100);
declare @Reporttype int;--内外网
--最终的查询
declare @AllSql varchar(8000);
--最终查询字段
declare @SqlField varchar(8000);
set @SqlField='';
set @AllSql='';
set @ID = 0;
set @spliti='';
set @ValueType = 0;
set @ConditionType = 0;
set @TemConditionType = -1;
set @Where=' Where 1=1 ';
set @AndOr = 'And';
set @FieldName = '';
--指标@IndicatorField
set @IndicatorField='';
set @NewTable='';
set @nullString=' ';
--生成表
declare @TabeName char(17);
declare @CreateTable varchar(7000);
--set @TabeName ='QueryTable_'+substring(cast(newid() as char(36)),0,8);
set @CreateTable = '';
--------------------------------
select @TabeName = QueryTable from QueryUnit Where Search_id = @SearchID;
select @Reporttype = ReportType from QueryUnit Where Search_id = @SearchID; --内外网
Select @AccountId=account_id,@TastID=Tast_ID from QueryUnit Where Search_id = @SearchID;
if(@Reporttype = 1)
Select @ReportName='ReportData_'+CAST(Report_id AS char(5)) from ReportTast where Tast_id=@TastID;
else
Select @ReportName='ReportOutData_'+CAST(Report_id AS char(5)) from ReportTast where Tast_id=@TastID;
select @fullcode= fullcode from unit where unit_id = (select Unit_id from account where account_id = @AccountId);
--**************************************************************************************
select @ReportID=report_id from reporttast where tast_id = @tastID;
select @SpecialID=Special_ID from report where report_id = @ReportID;
--select * from report
SELECT @FieldName = @FieldName+',unitExt.'+fieldName FROM SysField where Special_ID = @SpecialID and visible = 1 AND isGlobal=0 order by FieldIndex;
--**************************************************************************************************************************************
--Indicator_id, IndicatorCode, RefCode, PropertyID, RowNo, ColNo,DataValue ,unitExt.*
--如果临表存
if exists(select 1 from tempdb..sysobjects where name = N'##TemUnit' AND type = 'U')
drop table ##TemUnit
if exists(select 1 from tempdb..sysobjects where name = N'##TemIndicator' AND type = 'U')
drop table ##TemIndicator
if exists(select 1 from tempdb..sysobjects where name = N'##TemIndicatorData' AND type = 'U')
drop table ##TemIndicatorData
--查询单位列表
exec('
SELECT unit.Unit_ID ,Unit.TJCode,UnitCode,UnitName,pUnitName'+@FieldName+',Unit_Input.Input_ID
into ##TemUnit FROM Unit INNER JOIN
UnitExt ON Unit.Unit_ID = UnitExt.Unit_ID INNER JOIN
Unit_Input ON Unit.Unit_ID = Unit_Input.Unit_ID where Unit_Input.Tast_id='+@TastID+'');
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exec(' SELECT T1.input_id,T1.RowNo,T1.colNo,T1.DataValue into ##TemIndicatorData
FROM '+@ReportName+' T1 INNER JOIN
Unit_Input ON T1.Input_ID = Unit_Input.Input_ID
Where Tast_id='+@TastId+'');
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
declare @s1 varchar(8000);
declare @s2 varchar(8000);
declare @s3 varchar(8000);
declare @s4 varchar(8000);
declare @s5 varchar(8000);
declare @s6 varchar(8000);
declare @s7 varchar(8000);
declare @s8 varchar(8000);
declare @s9 varchar(8000);
declare @s10 varchar(8000);
set @s1 = '';
set @s2='';
set @s3='';
set @s4='';
set @s5='';
set @s6='';
set @s7='';
set @s8='';
set @s9='';
set @s10='';
select
@s1 = @s1+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo<=10
group by
RowNo,colNo
order by
RowNo,colNo;
------------------------------------------------------------------------------
set @s2 = '';
select
@s2 = @s2+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>10 and RowNo<=20
group by
RowNo,colNo
order by
RowNo,colNo;
-----------------------------------------------------------------------------
set @s3 = '';
select
@s3 = @s3+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>20 and RowNo<=30
group by
RowNo,colNo
order by
RowNo,colNo;
---------------------------------------------------------------------------------------------
set @s4 = '';
select
@s4 = @s4+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>30 and RowNo<=40
group by
RowNo,colNo
order by
RowNo,colNo;
--------------------------------------------------------------------------------
set @s5 = '';
select
@s5 = @s5+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>40 and RowNo<=50
group by
RowNo,colNo
order by
RowNo,colNo;
--------------------------------------------------------------------------------------------------
set @s6 = '';
select
@s6 = @s6+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>50 and RowNo<=60
group by
RowNo,colNo
order by
RowNo,colNo;
--------------------------------------------------------------------------------------------------
set @s7 = '';
select
@s7 = @s7+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>60 and RowNo<=70
group by
RowNo,colNo
order by
RowNo,colNo;
--------------------------------------------------------------------------------------------------
set @s8 = '';
select
@s8 = @s8+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>70 and RowNo<=80
group by
RowNo,colNo
order by
RowNo,colNo;
--------------------------------------------------------------------------------------------------
set @s9 = '';
select
@s9 = @s9+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>80 and RowNo<=90
group by
RowNo,colNo
order by
RowNo,colNo;
-------------------------------------------------------------------------------------------------
set @s1 = ' select input_id '+@s1;
set @s10 =@s10+' into ##TemIndicator from ##TemIndicatorData group by input_id order by input_id ';
exec(@s1+@s2+@s3+@s4+@s5+@s6+@s7+@s8+@s9+@s10);
--select * from ##TemUnit T1 inner join ##TemIndicator T2 on T1.input_id=T2.input_id;
--构建查询Where语句
DECLARE Condition_cursor CURSOR
for
SELECT [ID] FROM QueryCondition WHERE SearchID = @SearchID order by ConditionType DESC
open Condition_cursor
FETCH NEXT FROM Condition_cursor into @ID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SELECT
@ConFieldName=rtrim(ltrim(FieldName)),
@op=rtrim(ltrim(op)),
@ValueType = rtrim(ltrim(ValueType)),
@StartValue = rtrim(ltrim(StartValue)),
@EndValue = rtrim(ltrim(EndValue)),
@ConditionType = rtrim(ltrim(ConditionType))
FROM QueryCondition where [ID] = @ID;
if(@TemConditionType=-1)
begin
set @Where = @Where+' and (';
set @TemConditionType = @ConditionType;
end
else
begin
if(@TemConditionType <> @ConditionType)
begin
Set @Where = @Where +') '+ @nullString+@AndOr+' ('+@nullString;
set @TemConditionType = @ConditionType
end
else
begin
Set @Where = @Where + @nullString+@AndOr+@nullString;
end
end
if(charindex('/',@ConFieldName)>0)
begin
select top 1 @spliti=F1 from dbo.dhg_splitstr(@ConFieldName,'/') order by A1 desc;
--select substringspliti=subString(@spliti,charindex('[',@spliti)+2,charindex(']',@spliti));
--select substringspliti
--select * from ##TemIndicatorData where RowNo>80 and RowNo<=90 group by RowNo,colNo order by RowNo,colNo;
end
if(@ConditionType=0)--指标条件
begin
if(charindex('/',@ConFieldName)>0) --dhg 如果含有除号
begin
if(@ValueType=1)--区间
begin
set @Where = @Where+ '(case when '+ @spliti+' <>0 then '+ @ConFieldName+' end)' +@nullString+rtrim(ltrim(@op))+@nullString+rtrim(ltrim(@StartValue));
end
else
begin
set @Where = @Where+ '(case when '+ @spliti+' <>0 then '+ @ConFieldName+' end)' +@nullString+rtrim(ltrim(@op))+@nullString+rtrim(ltrim(@StartValue));
end
end
else
begin
if(@ValueType=1)--区间
begin
set @Where = @Where+ @ConFieldName +@nullString+rtrim(ltrim(@op))+@nullString+rtrim(ltrim(@StartValue));
end
else
begin
set @Where = @Where+ @ConFieldName +@nullString+rtrim(ltrim(@op))+@nullString+rtrim(ltrim(@StartValue));
end
end
end
else--单位条件
begin
if(charindex('/',@ConFieldName)>0) --dhg 如果含有除号
begin
if(@ValueType=1)--区间
begin
set @Where = @Where+'(case when '+ @spliti+' <>0 then '+ @ConFieldName+' end)'+@nullString+rtrim(ltrim(@op))+@nullString+''''+rtrim(ltrim(@StartValue))+'''';
end
else
begin
set @Where = @Where+'(case when '+ @spliti+' <>0 then '+ @ConFieldName+' end)'+@nullString+rtrim(ltrim(@op))+@nullString+''''+rtrim(ltrim(@StartValue))+'''';
end
end
else
begin
if(@ValueType=1)--区间
begin
set @Where = @Where+ @ConFieldName +@nullString+rtrim(ltrim(@op))+@nullString+''''+rtrim(ltrim(@StartValue))+'''';
end
else
begin
set @Where = @Where+ @ConFieldName+@nullString+rtrim(ltrim(@op))+@nullString+''''+rtrim(ltrim(@StartValue))+'''';
end
end
end
select @AndOr = rtrim(ltrim(AndOr)) FROM QueryCondition where [ID] = @ID;
FETCH NEXT FROM Condition_cursor into @ID
END
CLOSE Condition_cursor
DEALLOCATE Condition_cursor
if(@TemConditionType<>-1)
set @Where=@Where+' )';
set @SqlField=' UnitName,pUnitName';
--set @SqlField=' identity(int,1,1) as ID';
SELECT @SqlField = @SqlField+','+ rtrim(ltrim(QueryField))+' as '+ rtrim(ltrim(QueryAsField)) FROM QueryField Where SearchID=@SearchID;
set @AllSql ='select '+@SqlField+' from ##TemIndicator T1 inner join ##TemUnit T2 on T1.input_id = T2.Input_id';
--set @AllSql ='select '+@SqlField+' into '+@TabeName+' from ##TemIndicator T1 inner join ##TemUnit T2 on T1.input_id = T2.Input_id'+@Where
exec(@AllSql+@Where);
--select @AllSql;
--select @where;
代码的简单解释:
1、先通过SearchID获得表QueryUnit里面的TastID
Select @AccountId=account_id,@TastID=Tast_ID from QueryUnit Where Search_id = @SearchID;
2、然后根据TastID去找Unit表及UnitExt、Unit_Input表里的数据后并创建一个临时表##TemUnit
SELECT unit.Unit_ID ,Unit.TJCode,UnitCode,UnitName,pUnitName'+@FieldName+',Unit_Input.Input_ID
into ##TemUnit FROM Unit INNER JOIN
UnitExt ON Unit.Unit_ID = UnitExt.Unit_ID INNER JOIN
Unit_Input ON Unit.Unit_ID = Unit_Input.Unit_ID where Unit_Input.Tast_id='+@TastID+'');
用exec运行：
@FieldName等于
,unitExt.s1,unitExt.s2,unitExt.s3,unitExt.s4,unitExt.s5,unitExt.s6,unitExt.s7,
unitExt.s8,unitExt.s9,unitExt.s10,unitExt.s11,unitExt.s12,unitExt.s13,unitExt.s14,
unitExt.s15,unitExt.s16,unitExt.s17,unitExt.s18,unitExt.s19,unitExt.s20,
unitExt.s21,unitExt.s22'
------------------------------------------------------------------------
SELECT unit.Unit_ID ,Unit.TJCode,UnitCode,UnitName,pUnitName'+@FieldName+',Unit_Input.Input_ID
into ##TemUnit FROM Unit INNER JOIN
UnitExt ON Unit.Unit_ID = UnitExt.Unit_ID INNER JOIN
Unit_Input ON Unit.Unit_ID = Unit_Input.Unit_ID where Unit_Input.Tast_id='+@TastID+'');
3、根据SearchID来获得表QueryUnit中的Reporttype字段的数据。
select @Reporttype = ReportType from QueryUnit Where Search_id = @SearchID; --内外网
根据Reporttype字段来判断是外网还是内网中的数据。
如果Reporttype=1为内网数据表，否则为外网数据表。ReportName为表名
if(@Reporttype = 1)
Select @ReportName='ReportData_'+CAST(Report_id AS char(5)) from ReportTast where Tast_id=@TastID;
else
Select @ReportName='ReportOutData_'+CAST(Report_id AS char(5)) from ReportTast where Tast_id=@TastID;
4、同时根据TastID去找表 及Unit_Input的数据后并创建一个临时表##TemIndicatorData.
SELECT T1.input_id,T1.RowNo,T1.colNo,T1.DataValue into ##TemIndicatorData
FROM '+@ReportName+' T1 INNER JOIN
Unit_Input ON T1.Input_ID = Unit_Input.Input_ID
Where Tast_id='+@TastId+'
5、动态生成一组新的SQL字符串。
S1:
select
@s1 = @s1+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo<=10
group by
RowNo,colNo
order by
RowNo,colNo;
--------------------------------------------------------------------------
S2:
set @s2 = '';
select
@s2 = @s2+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>10 and RowNo<=20
group by
RowNo,colNo
order by
RowNo,colNo;
--------------------------------------------------------------------------
S3:
set @s3 = '';
select
@s3 = @s3+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>20 and RowNo<=30
group by
RowNo,colNo
order by
RowNo,colNo;
---------------------------------------------------------------------------
S4:
set @s4 = '';
select
@s4 = @s4+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>30 and RowNo<=40
group by
RowNo,colNo
order by
RowNo,colNo;
-----------------------------------------------------------------------------
S5:
set @s5 = '';
select
@s5 = @s5+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>40 and RowNo<=50
group by
RowNo,colNo
order by
RowNo,colNo;
---------------------------------------------------------------------------------
S6:
set @s6 = '';
select
@s6 = @s6+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>50 and RowNo<=60
group by
RowNo,colNo
order by
RowNo,colNo;
----------------------------------------------------------------------------------
S7:
set @s7 = '';
select
@s7 = @s7+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>60 and RowNo<=70
group by
RowNo,colNo
order by
RowNo,colNo;
--------------------------------------------------------------------------------------------
S8:
set @s8 = '';
select
@s8 = @s8+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>70 and RowNo<=80
group by
RowNo,colNo
order by
RowNo,colNo;
---------------------------------------------------------------------------------------
S9:
set @s9 = '';
select
@s9 = @s9+',['
+rtrim(RowNo)+'_'+rtrim(colNo)+']=max(case when RowNo='
+rtrim(RowNo)+' and colNo='+rtrim(colNo)+' then DataValue end)'
from
##TemIndicatorData
where
RowNo>80 and RowNo<=90
group by
RowNo,colNo
order by
RowNo,colNo;
-----------------------------------------------------------------------------------------
S10:
set @s1 = ' select input_id '+@s1;
set @s10 =@s10+' into ##TemIndicator from ##TemIndicatorData group by input_id order by input_id ';
------------------------------------------------------------------------------------------------------------------------
运行上面程序：
exec(@s1+@s2+@s3+@s4+@s5+@s6+@s7+@s8+@s9+@s10);
上面的程序运行时等同下面程序：（查询##TemIndicatorData表并临时创建一个临时表TemIndicator）
select input_id,[1_1]=max(case when RowNo=1 and colNo=1 then DataValue end),
[1_2]=max(case when RowNo=1 and colNo=2 then DataValue end),
[2_1]=max(case when RowNo=2 and colNo=1 then DataValue end),
[2_2]=max(case when RowNo=2 and colNo=2 then DataValue end),
[11_1]=max(case when RowNo=11 and colNo=1 then DataValue end),
[11_2]=max(case when RowNo=11 and colNo=2 then DataValue end),
[12_1]=max(case when RowNo=12 and colNo=1 then DataValue end),
[12_2]=max(case when RowNo=12 and colNo=2 then DataValue end),
[21_1]=max(case when RowNo=21 and colNo=1 then DataValue end),
[21_2]=max(case when RowNo=21 and colNo=2 then DataValue end),
[22_1]=max(case when RowNo=22 and colNo=1 then DataValue end),
[22_2]=max(case when RowNo=22 and colNo=2 then DataValue end),
[31_1]=max(case when RowNo=31 and colNo=1 then DataValue end),
[31_2]=max(case when RowNo=31 and colNo=2 then DataValue end),
[32_1]=max(case when RowNo=32 and colNo=1 then DataValue end),
[32_2]=max(case when RowNo=32 and colNo=2 then DataValue end)
into ##TemIndicator
from ##TemIndicatorData group by input_id order by input_id
6、--构建查询Where语句
