/****** 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

解决方案 »

  1.   

    性能关键在于那些动态SQL,
    建议LZ把动态SQL都print出来,然后贴一下,说明一下数据来源表的数据量及索引情况.
    以便帮你分析.
      

  2.   


    --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)
      

  3.   

    这种情况,不用临时表。另外,这种存储过程,如果经常执行,最好不用动态sql语句。按照 @OrderBy的值,可以考虑分别写5个sql语句。这样,以后维护的时候,别人也能看懂哈。不然,这种动态语句,过了N天后,自己都有可能看不懂滴
      

  4.   


    --请修改成使用参数化语句,使用以下命名执行.exec sp_executesql @sql