/****** Object: StoredProcedure [dbo].[ProcQueryFourPolluteLetCollect_TJ]
ALTER PROCEDURE [dbo].[ProcQueryFourPolluteLetCollect_TJ]
-- 查询参数定义
@DataMonthStart varchar(6), --起始月份
@DataMonthEnd varchar(6), --截至月份
@RegionCode varchar(10), --行政区划编码
@IfThirtyTenthousandkilowat int, --是否30万千瓦电力企业
@AttentionDegreeCode int, --关注程度(国控)
@OrderBy int --排序(1=二氧化硫,2=氮氧化物,3=COD,4=氨氮)
ASBEGIN
CREATE TABLE #temp --创建临时表
(
_RegionName varchar(50),
_LetCapacity1 decimal(12,2),
_LetCapacity2 decimal(12,2),
_LetCapacity3 decimal(12,2),
_LetCapacity4 decimal(12,2)
);
Declare @QuerySql varchar(8000);
SET @QuerySql = 'SELECT RegionName AS 所属环保机构,SUM(ISNULL(二氧化硫,0)) AS 二氧化硫,SUM(ISNULL(氮氧化物,0)) AS 氮氧化物 ,SUM(ISNULL([化学需氧量(COD)],0)) AS [化学需氧量(COD)] ,SUM(ISNULL(氨氮,0)) AS 氨氮 from(select RegionName,PollutantName AS 污染物,PollutantCode,LetCapacity AS 排污量 from(('
Declare @Sql varchar(8000);
IF(@DataMonthStart !='' AND @DataMonthEnd !='' AND @DataMonthStart <= @DataMonthEnd )
BEGIN
SET @Sql = ' AND DataMonth BETWEEN '+@DataMonthStart+' AND '+@DataMonthEnd+''
END
--获取所在以及下属的所有环保机构
declare @RegionSql varchar(8000);
SET @RegionSql='(SELECT RegionCode FROM dictionary.region WHERE RegionCode='+@RegionCode+' AND DeleteFlag =0 union SELECT RegionCode FROM dictionary.region WHERE ParentNode in(SELECT RegionCode FROM dictionary.region WHERE ParentNode='+@RegionCode+') and DeleteFlag =0)'
begin
set @Sql = @Sql + ' AND r.RegionCode IN' + @RegionSql
end
--是否30万千瓦电力企业--
IF(@IfThirtyTenthousandkilowat!='' AND @IfThirtyTenthousandkilowat =1)
BEGIN
SET @Sql+=' AND IfThirtyTenthousandkilowat=1'
END
--关注程度(国控)--
IF(@AttentionDegreeCode!='' AND @AttentionDegreeCode =1)
BEGIN
SET @Sql +=' AND AttentionDegreeCode =1'
END
SET @QuerySql =@QuerySql+ ' SELECT w.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,w.[PollutantCode],d.PollutantName,SUM(ISNULL([LetCapacity],0)) AS [LetCapacity] FROM [BaseOperationData].[WaterOutputPollutionMonthLet] w,PSInfo.PSBaseInfo p,Dictionary.Region r,Dictionary.PollutantCode d Where w.PSCode =p.PSCode AND r.RegionCode = p.RegionCode AND d.PollutantCode =w.PollutantCode AND p.Status=0 AND p.DeleteFlag=0 AND w.DeleteFlag=0 AND d.PollutantTypeCode IN(1,2) AND w.[PollutantCode]=011 AND d.PollutantTypeCode=1 '+@Sql+' GROUP BY w.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,w.[PollutantCode],d.PollutantName)union(SELECT w.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,w.[PollutantCode],d.PollutantName,SUM(ISNULL([LetCapacity],0))AS [LetCapacity] FROM [BaseOperationData].[WaterOutputPollutionMonthLet] w,PSInfo.PSBaseInfo p,Dictionary.Region r,Dictionary.PollutantCode d Where w.PSCode =p.PSCode AND r.RegionCode = p.RegionCode AND d.PollutantCode =w.PollutantCode AND p.Status=0 AND p.DeleteFlag=0 AND w.DeleteFlag=0 AND d.PollutantTypeCode IN(1,2) AND w.[PollutantCode]=060 AND d.PollutantTypeCode=1 '+@Sql+' GROUP BY w.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,w.[PollutantCode],d.PollutantName)union(SELECT g.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,g.[PollutantCode],d.PollutantName,SUM(ISNULL([LetCapacity],0))AS [LetCapacity] FROM [BaseOperationData].[GasOutputPollutionMonthLet] g,PSInfo.PSBaseInfo p,Dictionary.Region r,Dictionary.PollutantCode d Where g.PSCode =p.PSCode AND r.RegionCode = p.RegionCode AND d.PollutantCode =g.PollutantCode AND p.Status=0 AND p.DeleteFlag=0 AND g.DeleteFlag=0 AND d.PollutantTypeCode IN(1,2) AND g.[PollutantCode]=002 AND d.PollutantTypeCode=2'+@Sql+' GROUP BY g.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,g.[PollutantCode],d.PollutantName)union(SELECT g.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,g.[PollutantCode],d.PollutantName,SUM(ISNULL([LetCapacity],0))AS [LetCapacity] FROM [BaseOperationData].[GasOutputPollutionMonthLet] g,PSInfo.PSBaseInfo p,Dictionary.Region r,Dictionary.PollutantCode d Where g.PSCode =p.PSCode AND r.RegionCode = p.RegionCode AND d.PollutantCode =g.PollutantCode AND p.Status=0 AND p.DeleteFlag=0 AND g.DeleteFlag=0 AND d.PollutantTypeCode IN(1,2) AND g.[PollutantCode]=003 AND d.PollutantTypeCode=2 '+@Sql+' GROUP BY g.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,g.[PollutantCode],d.PollutantName))AS f)d pivot(SUM(排污量)for 污染物 in(二氧化硫,氮氧化物,[化学需氧量(COD)],氨氮))q GROUP BY RegionName'
print @QuerySql
--exec(@QuerySql)
declare @tempSql varchar(8000);
set @tempSql ='insert into #temp(_RegionName,_LetCapacity1,_LetCapacity2,_LetCapacity3,_LetCapacity4) select 所属环保机构,二氧化硫,氮氧化物,[化学需氧量(COD)],氨氮 from ('+@QuerySql+') a'
print @tempSql
exec (@tempSql)
--排序
IF(@OrderBy!='' AND @OrderBy=1)
SELECT * from #temp ORDER BY _LetCapacity1 DESC
ELSE IF(@OrderBy!='' AND @OrderBy=2)
SELECT * from #temp ORDER BY _LetCapacity2 DESC
ELSE IF(@OrderBy!='' AND @OrderBy=3)
SELECT * from #temp ORDER BY _LetCapacity3 DESC
ELSE IF(@OrderBy!='' AND @OrderBy=4)
SELECT * from #temp ORDER BY _LetCapacity4 DESC
ELSE
SELECT * from #temp
END存储sqlpivotselect
ALTER PROCEDURE [dbo].[ProcQueryFourPolluteLetCollect_TJ]
-- 查询参数定义
@DataMonthStart varchar(6), --起始月份
@DataMonthEnd varchar(6), --截至月份
@RegionCode varchar(10), --行政区划编码
@IfThirtyTenthousandkilowat int, --是否30万千瓦电力企业
@AttentionDegreeCode int, --关注程度(国控)
@OrderBy int --排序(1=二氧化硫,2=氮氧化物,3=COD,4=氨氮)
ASBEGIN
CREATE TABLE #temp --创建临时表
(
_RegionName varchar(50),
_LetCapacity1 decimal(12,2),
_LetCapacity2 decimal(12,2),
_LetCapacity3 decimal(12,2),
_LetCapacity4 decimal(12,2)
);
Declare @QuerySql varchar(8000);
SET @QuerySql = 'SELECT RegionName AS 所属环保机构,SUM(ISNULL(二氧化硫,0)) AS 二氧化硫,SUM(ISNULL(氮氧化物,0)) AS 氮氧化物 ,SUM(ISNULL([化学需氧量(COD)],0)) AS [化学需氧量(COD)] ,SUM(ISNULL(氨氮,0)) AS 氨氮 from(select RegionName,PollutantName AS 污染物,PollutantCode,LetCapacity AS 排污量 from(('
Declare @Sql varchar(8000);
IF(@DataMonthStart !='' AND @DataMonthEnd !='' AND @DataMonthStart <= @DataMonthEnd )
BEGIN
SET @Sql = ' AND DataMonth BETWEEN '+@DataMonthStart+' AND '+@DataMonthEnd+''
END
--获取所在以及下属的所有环保机构
declare @RegionSql varchar(8000);
SET @RegionSql='(SELECT RegionCode FROM dictionary.region WHERE RegionCode='+@RegionCode+' AND DeleteFlag =0 union SELECT RegionCode FROM dictionary.region WHERE ParentNode in(SELECT RegionCode FROM dictionary.region WHERE ParentNode='+@RegionCode+') and DeleteFlag =0)'
begin
set @Sql = @Sql + ' AND r.RegionCode IN' + @RegionSql
end
--是否30万千瓦电力企业--
IF(@IfThirtyTenthousandkilowat!='' AND @IfThirtyTenthousandkilowat =1)
BEGIN
SET @Sql+=' AND IfThirtyTenthousandkilowat=1'
END
--关注程度(国控)--
IF(@AttentionDegreeCode!='' AND @AttentionDegreeCode =1)
BEGIN
SET @Sql +=' AND AttentionDegreeCode =1'
END
SET @QuerySql =@QuerySql+ ' SELECT w.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,w.[PollutantCode],d.PollutantName,SUM(ISNULL([LetCapacity],0)) AS [LetCapacity] FROM [BaseOperationData].[WaterOutputPollutionMonthLet] w,PSInfo.PSBaseInfo p,Dictionary.Region r,Dictionary.PollutantCode d Where w.PSCode =p.PSCode AND r.RegionCode = p.RegionCode AND d.PollutantCode =w.PollutantCode AND p.Status=0 AND p.DeleteFlag=0 AND w.DeleteFlag=0 AND d.PollutantTypeCode IN(1,2) AND w.[PollutantCode]=011 AND d.PollutantTypeCode=1 '+@Sql+' GROUP BY w.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,w.[PollutantCode],d.PollutantName)union(SELECT w.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,w.[PollutantCode],d.PollutantName,SUM(ISNULL([LetCapacity],0))AS [LetCapacity] FROM [BaseOperationData].[WaterOutputPollutionMonthLet] w,PSInfo.PSBaseInfo p,Dictionary.Region r,Dictionary.PollutantCode d Where w.PSCode =p.PSCode AND r.RegionCode = p.RegionCode AND d.PollutantCode =w.PollutantCode AND p.Status=0 AND p.DeleteFlag=0 AND w.DeleteFlag=0 AND d.PollutantTypeCode IN(1,2) AND w.[PollutantCode]=060 AND d.PollutantTypeCode=1 '+@Sql+' GROUP BY w.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,w.[PollutantCode],d.PollutantName)union(SELECT g.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,g.[PollutantCode],d.PollutantName,SUM(ISNULL([LetCapacity],0))AS [LetCapacity] FROM [BaseOperationData].[GasOutputPollutionMonthLet] g,PSInfo.PSBaseInfo p,Dictionary.Region r,Dictionary.PollutantCode d Where g.PSCode =p.PSCode AND r.RegionCode = p.RegionCode AND d.PollutantCode =g.PollutantCode AND p.Status=0 AND p.DeleteFlag=0 AND g.DeleteFlag=0 AND d.PollutantTypeCode IN(1,2) AND g.[PollutantCode]=002 AND d.PollutantTypeCode=2'+@Sql+' GROUP BY g.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,g.[PollutantCode],d.PollutantName)union(SELECT g.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,g.[PollutantCode],d.PollutantName,SUM(ISNULL([LetCapacity],0))AS [LetCapacity] FROM [BaseOperationData].[GasOutputPollutionMonthLet] g,PSInfo.PSBaseInfo p,Dictionary.Region r,Dictionary.PollutantCode d Where g.PSCode =p.PSCode AND r.RegionCode = p.RegionCode AND d.PollutantCode =g.PollutantCode AND p.Status=0 AND p.DeleteFlag=0 AND g.DeleteFlag=0 AND d.PollutantTypeCode IN(1,2) AND g.[PollutantCode]=003 AND d.PollutantTypeCode=2 '+@Sql+' GROUP BY g.[PSCode],p.PSName,r.RegionName,p.PSAddress,p.LinkMan,g.[PollutantCode],d.PollutantName))AS f)d pivot(SUM(排污量)for 污染物 in(二氧化硫,氮氧化物,[化学需氧量(COD)],氨氮))q GROUP BY RegionName'
print @QuerySql
--exec(@QuerySql)
declare @tempSql varchar(8000);
set @tempSql ='insert into #temp(_RegionName,_LetCapacity1,_LetCapacity2,_LetCapacity3,_LetCapacity4) select 所属环保机构,二氧化硫,氮氧化物,[化学需氧量(COD)],氨氮 from ('+@QuerySql+') a'
print @tempSql
exec (@tempSql)
--排序
IF(@OrderBy!='' AND @OrderBy=1)
SELECT * from #temp ORDER BY _LetCapacity1 DESC
ELSE IF(@OrderBy!='' AND @OrderBy=2)
SELECT * from #temp ORDER BY _LetCapacity2 DESC
ELSE IF(@OrderBy!='' AND @OrderBy=3)
SELECT * from #temp ORDER BY _LetCapacity3 DESC
ELSE IF(@OrderBy!='' AND @OrderBy=4)
SELECT * from #temp ORDER BY _LetCapacity4 DESC
ELSE
SELECT * from #temp
END存储sqlpivotselect
建议LZ把动态SQL都print出来,然后贴一下,说明一下数据来源表的数据量及索引情况.
以便帮你分析.
--1.去掉region修改,建议通过连表来实现。
DECLARE @RegionSql VARCHAR(8000);
SET @RegionSql = '(
SELECT RegionCode
FROM dictionary.region
WHERE ParentNode in
(
SELECT RegionCode
FROM dictionary.region
WHERE ParentNode='
+ @RegionCode + '
)
and DeleteFlag =0)'2.@QuerySql,将4个语句合并。SET @QuerySql = @QuerySql
+ '
SELECT w.[PSCode] ,
p.PSName ,
r.RegionName ,
p.PSAddress ,
p.LinkMan ,
w.[PollutantCode] ,
d.PollutantName ,
SUM(ISNULL([LetCapacity], 0)) AS [LetCapacity]
FROM [BaseOperationData].[WaterOutputPollutionMonthLet] w ,
PSInfo.PSBaseInfo p ,
Dictionary.Region r ,
Dictionary.PollutantCode d
WHERE w.PSCode = p.PSCode
AND r.RegionCode = p.RegionCode
AND d.PollutantCode = w.PollutantCode
AND p.Status = 0
AND p.DeleteFlag = 0
AND w.DeleteFlag = 0
AND d.PollutantTypeCode IN ( 1, 2 )
AND d.PollutantTypeCode = 1
AND w.[PollutantCode] IN ( 011, 060, 002, 003 )'--合并起来
+ @Sql--3.排序,数据不需要写入临时表,直接在查询最后加order by DECLARE @tempSql VARCHAR(8000);
SET @tempSql = '
SELECT 所属环保机构,二氧化硫,氮氧化物,[化学需氧量(COD)],氨氮
FROM (' + @QuerySql + ') a 'IF ( @OrderBy BETWEEN 1 AND 4)
BEGIN
SET @tempSql=@tempSql +' ORDER BY '+LTRIM(@OrderBy+1)
ENDEXEC (@tempSql)
--请修改成使用参数化语句,使用以下命名执行.exec sp_executesql @sql