文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >查询优化？

问查询优化？
EN

Stack Overflow用户
提问于 2013-12-17 08:01:34
回答 3查看 127关注 0票数 2

大家好，我有12个查询和一些c#代码来使页面上的数据格式化，但现在我们将这些页面转换为SSRS报告。我已经构建了存储过程，它结合了12个查询和一些新的附加查询来格式化ssrs报告中的数据。现在，新的存储过程比旧的页面花费更多的时间，这里是我的查询，可以对以下存储过程进行任何优化，任何帮助都是很棒的。

代码语言：javascript
复制
CREATE PROCEDURE [dbo].[GetHistoryByYear_Get] 
-- Add the parameters for the stored procedure here    
@Year AS VARCHAR(4),
@PreYear AS VARCHAR(4)
AS
BEGIN
SET NOCOUNT ON;  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT *  
INTO #tempCustVol
FROM 
    ( 
SELECT *  FROM 
    (   
        SELECT * FROM (
        SELECT @Year AS 'Year',Company, Customer,SUM(Jan) AS Jan, SUM(Feb) AS Feb, SUM(Mar) As Mar, SUM(Apr) AS Apr, SUM(May) AS May, SUM(Jun) AS Jun, SUM(Jul) AS Jul, SUM(Aug) AS Aug, SUM(Sep) AS Sep, SUM(Oct) AS Oct, SUM(Nov) AS Nov, SUM(Dec) AS Dec    
        ,(SUM(Jan) + SUM(Feb) + SUM(Mar) + SUM(Apr) + SUM(May) + SUM(Jun) + SUM(Jul) + SUM(Aug) + SUM(Sep) + SUM(Oct) + SUM(Nov) + SUM(Dec) ) AS YearlyTotal    
        FROM(    
            SELECT Company, Customer, DateRcvd, SUM(Records) AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '01' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, SUM(RECORDS) AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '02' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, SUM(RECORDS) As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '03' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, SUM(RECORDS) As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '04' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, SUM(RECORDS) As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '05' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, SUM(RECORDS) As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '06' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, SUM(RECORDS) As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '07' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, SUM(RECORDS) As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '08' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, SUM(RECORDS) As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '09' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, SUM(RECORDS) As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '10' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, SUM(RECORDS) As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '11' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, SUM(RECORDS) As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '12' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            ) F    
        GROUP BY Company, Customer
        ) AS ALLDATA

        UNION 

        SELECT * FROM (
        SELECT @PreYear AS 'Year',Company, Customer,SUM(Jan) AS Jan, SUM(Feb) AS Feb, SUM(Mar) As Mar, SUM(Apr) AS Apr, SUM(May) AS May, SUM(Jun) AS Jun, SUM(Jul) AS Jul, SUM(Aug) AS Aug, SUM(Sep) AS Sep, SUM(Oct) AS Oct, SUM(Nov) AS Nov, SUM(Dec) AS Dec    
        ,(SUM(Jan) + SUM(Feb) + SUM(Mar) + SUM(Apr) + SUM(May) + SUM(Jun) + SUM(Jul) + SUM(Aug) + SUM(Sep) + SUM(Oct) + SUM(Nov) + SUM(Dec) ) AS YearlyTotal    
        FROM(    
            SELECT Company, Customer, DateRcvd, SUM(Records) AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '01' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, SUM(RECORDS) AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '02' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, SUM(RECORDS) As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '03' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, SUM(RECORDS) As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '04' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, SUM(RECORDS) As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '05' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, SUM(RECORDS) As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '06' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, SUM(RECORDS) As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '07' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, SUM(RECORDS) As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '08' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, SUM(RECORDS) As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '09' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, SUM(RECORDS) As Oct, 0 As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '10' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, SUM(RECORDS) As Nov, 0 As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '11' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            UNION    
            SELECT Company, Customer, DateRcvd, 0 AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, SUM(RECORDS) As Dec    
            FROM(    
                select * from vwjmrep where datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231' AND SUBSTRING(DateRcvd,5,2) = '12' AND Company IS NOT NULL    
                )  AS T    
            GROUP BY Company, Customer, DateRcvd    
            ) F    
        GROUP BY Company, Customer
        ) AS ALLDATA
    ) AS TEMPDATA
    ) AS data

SELECT * FROM (SELECT * FROM #tempCustVol
UNION
SELECT 
    @PreYear AS [Year],null,null,COALESCE(SUM(Jan),0),COALESCE(SUM(Feb),0),COALESCE(SUM(Mar),0),COALESCE(SUM(Apr),0),
    COALESCE(SUM(May),0),COALESCE(SUM(Jun),0),COALESCE(SUM(Jul),0),COALESCE(SUM(Aug),0),COALESCE(SUM(Sep),0),COALESCE(SUM(Oct),0),
    COALESCE(SUM(Nov),0),COALESCE(SUM(Dec),0),COALESCE((SUM(Jan) + SUM(Feb) + SUM(Mar) + SUM(Apr) + SUM(May) + SUM(Jun) + SUM(Jul) + SUM(Aug) + SUM(Sep) + SUM(Oct) + SUM(Nov) + SUM(Dec) ),0) AS YearlyTotal
FROM #tempCustVol
WHERE [Year] = @PreYear
)AS DA
ORDER BY 
    CASE WHEN Company is null THEN 1 ELSE 0 END,
    Company,[Year]

DROP TABLE #tempCustVol
END

任何帮助都会很好，我已经索引了表和表，有很多数据，它需要大约10到12分钟来执行，有任何方法我可以最小化它。它是SERVER 2008数据库

更新

这是我更新的存储过程

代码语言：javascript
复制
BEGIN
DECLARE @Year AS VARCHAR(4),
@PreYear AS VARCHAR(4)
SET  @Year='2013'
SET @PreYear='2012'
SET NOCOUNT ON;  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT *  
INTO #tempCustVol
FROM 
    ( 
SELECT *  FROM 
    (   
        SELECT * FROM (
            SELECT @Year AS 'Year',Company, Customer,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '01' THEN Records ELSE 0 END) AS Jan,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '02' THEN Records ELSE 0 END) AS Feb,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '03' THEN Records ELSE 0 END) AS Mar,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '04' THEN Records ELSE 0 END) AS Apr,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '05' THEN Records ELSE 0 END) AS May,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '06' THEN Records ELSE 0 END) AS Jun,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '07' THEN Records ELSE 0 END) AS Jul,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '08' THEN Records ELSE 0 END) AS Aug,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '09' THEN Records ELSE 0 END) AS Sep,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '10' THEN Records ELSE 0 END) AS Oct,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '11' THEN Records ELSE 0 END) AS Nov,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '12' THEN Records ELSE 0 END) AS Dec,
            SUM(Records) AS YearlyTotal    
        FROM  vwjmrep
        WHERE datercvd >=@Year + '0101' and datercvd <= @Year + '1231'  AND Company IS NOT NULL 
        GROUP BY Company, Customer
        ) AS ALLDATA
        UNION ALL
        SELECT * FROM (
        SELECT @PreYear AS 'Year',Company, Customer,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '01' THEN Records ELSE 0 END) AS Jan,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '02' THEN Records ELSE 0 END) AS Feb,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '03' THEN Records ELSE 0 END) AS Mar,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '04' THEN Records ELSE 0 END) AS Apr,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '05' THEN Records ELSE 0 END) AS May,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '06' THEN Records ELSE 0 END) AS Jun,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '07' THEN Records ELSE 0 END) AS Jul,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '08' THEN Records ELSE 0 END) AS Aug,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '09' THEN Records ELSE 0 END) AS Sep,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '10' THEN Records ELSE 0 END) AS Oct,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '11' THEN Records ELSE 0 END) AS Nov,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '12' THEN Records ELSE 0 END) AS Dec,
            SUM(Records) AS YearlyTotal    
        FROM  vwjmrep
        WHERE datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231'  AND Company IS NOT NULL 
        GROUP BY Company, Customer
        ) AS ALLDATA
    ) AS TEMPDATA
    ) AS data

SELECT * FROM (SELECT * FROM #tempCustVol
UNION ALL
SELECT 
    @PreYear AS [Year],null,null,COALESCE(SUM(Jan),0),COALESCE(SUM(Feb),0),COALESCE(SUM(Mar),0),COALESCE(SUM(Apr),0),
    COALESCE(SUM(May),0),COALESCE(SUM(Jun),0),COALESCE(SUM(Jul),0),COALESCE(SUM(Aug),0),COALESCE(SUM(Sep),0),COALESCE(SUM(Oct),0),
    COALESCE(SUM(Nov),0),COALESCE(SUM(Dec),0),COALESCE((SUM(Jan) + SUM(Feb) + SUM(Mar) + SUM(Apr) + SUM(May) + SUM(Jun) + SUM(Jul) + SUM(Aug) + SUM(Sep) + SUM(Oct) + SUM(Nov) + SUM(Dec) ),0) AS YearlyTotal
FROM #tempCustVol
WHERE [Year] = @PreYear
)AS DA
ORDER BY 
    CASE WHEN Company is null THEN 1 ELSE 0 END,
    Company,[Year]

DROP TABLE #tempCustVol
END

这仍然需要时间，但是否还有更多的优化可能，谢谢

sql
sql-server
stored-procedures
query-optimization
query-performance
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2013-12-17 08:27:46

我想，你可以试着替换这个

代码语言：javascript
复制
SELECT * FROM (
    SELECT @Year AS 'Year',Company, Customer,SUM(Jan) AS Jan, SUM(Feb) AS Feb, SUM(Mar) As Mar, SUM(Apr) AS Apr, SUM(May) AS May, SUM(Jun) AS Jun, SUM(Jul) AS Jul, SUM(Aug) AS Aug, SUM(Sep) AS Sep, SUM(Oct) AS Oct, SUM(Nov) AS Nov, SUM(Dec) AS Dec    
    ,(SUM(Jan) + SUM(Feb) + SUM(Mar) + SUM(Apr) + SUM(May) + SUM(Jun) + SUM(Jul) + SUM(Aug) + SUM(Sep) + SUM(Oct) + SUM(Nov) + SUM(Dec) ) AS YearlyTotal    
    FROM(    
        SELECT Company, Customer, DateRcvd, SUM(Records) AS Jan, 0 AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
        FROM(    
            select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '01' AND Company IS NOT NULL    
            )  AS T    
        GROUP BY Company, Customer, DateRcvd    
        UNION    
        SELECT Company, Customer, DateRcvd, 0 AS Jan, SUM(RECORDS) AS Feb, 0 As Mar, 0 As Apr, 0 As May, 0 As Jun, 0 As Jul, 0 As Aug, 0 As Sep, 0 As Oct, 0 As Nov, 0 As Dec    
        FROM(    
            select * from vwjmrep where datercvd >=@Year + '0101' and datercvd <= @Year + '1231' AND SUBSTRING(DateRcvd,5,2) = '02' AND Company IS NOT NULL    
            )  AS T    
        GROUP BY Company, Customer, DateRcvd   

GROUP BY Company, Customer

有了这个

代码语言：javascript
复制
 SELECT @Year AS 'Year',Company, Customer,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '01' THEN Records ELSE 0 END) AS Jan,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '02' THEN Records ELSE 0 END) AS Feb,
    ...,
    SUM(Records) AS YearlyTotal    
    FROM  vwjmrep
WHERE datercvd >=@Year + '0101' and datercvd <= @Year + '1231'  AND Company IS NOT NULL 
GROUP BY Company, Customer

至少，会有更少的表扫描。

票数 1
EN

Stack Overflow用户

发布于 2013-12-17 08:30:57

看看这个

代码语言：javascript
复制
CREATE PROCEDURE [dbo].[GetHistoryByYear_Get] 
-- Add the parameters for the stored procedure here    
@Year AS VARCHAR(4),
@PreYear AS VARCHAR(4)
AS
BEGIN
SET NOCOUNT ON;  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT *  
INTO #tempCustVol
FROM 
( 
SELECT *
        , Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + [Dec] AS YearlyTotal 
FROM (
SELECT @Year AS 'Year', Company, Customer,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '01' THEN Records ELSE 0)) Jan,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '02' THEN Records ELSE 0)) Feb,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '03' THEN Records ELSE 0)) Mar,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '04' THEN Records ELSE 0)) Apr,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '05' THEN Records ELSE 0)) May,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '06' THEN Records ELSE 0)) Jun,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '07' THEN Records ELSE 0)) Jul,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '08' THEN Records ELSE 0)) Aug,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '09' THEN Records ELSE 0)) Sep,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '10' THEN Records ELSE 0)) Oct,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '11' THEN Records ELSE 0)) Nov,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '12' THEN Records ELSE 0)) [Dec]
FROM vwjmrep
WHERE   Company IS NOT NULL
        AND (datercvd >=@Year + '0101' AND datercvd <= @Year + '1231')
GROUP BY Company, Customer
UNION ALL
SELECT @PreYear AS 'Year', Company, Customer,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '01' THEN Records ELSE 0)) Jan,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '02' THEN Records ELSE 0)) Feb,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '03' THEN Records ELSE 0)) Mar,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '04' THEN Records ELSE 0)) Apr,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '05' THEN Records ELSE 0)) May,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '06' THEN Records ELSE 0)) Jun,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '07' THEN Records ELSE 0)) Jul,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '08' THEN Records ELSE 0)) Aug,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '09' THEN Records ELSE 0)) Sep,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '10' THEN Records ELSE 0)) Oct,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '11' THEN Records ELSE 0)) Nov,
    SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '12' THEN Records ELSE 0)) [Dec]
FROM vwjmrep
WHERE   Company IS NOT NULL
        AND (datercvd >=@PreYear + '0101' AND datercvd <= @PreYear + '1231')
GROUP BY Company, Customer
) x
) ALLDATA
...Rest of the code here
票数 1
EN

Stack Overflow用户

发布于 2013-12-17 10:51:33

关于我的最后一条评论：-我仍然使用it.Doing，有些是sql中的东西，有些是C#中的东西，特别是当查询太重时，您的查询没有pagination.So，如果使用索引超过100 rows.Also，则没有optmize查询可以帮助。好的，伙计，如果它不运行，那就告诉我

代码语言：javascript
复制
;With CTE as
(SELECT @Year AS 'Year',Company, Customer,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '01' THEN Records ELSE 0 END) AS Jan,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '02' THEN Records ELSE 0 END) AS Feb,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '03' THEN Records ELSE 0 END) AS Mar,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '04' THEN Records ELSE 0 END) AS Apr,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '05' THEN Records ELSE 0 END) AS May,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '06' THEN Records ELSE 0 END) AS Jun,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '07' THEN Records ELSE 0 END) AS Jul,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '08' THEN Records ELSE 0 END) AS Aug,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '09' THEN Records ELSE 0 END) AS Sep,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '10' THEN Records ELSE 0 END) AS Oct,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '11' THEN Records ELSE 0 END) AS Nov,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '12' THEN Records ELSE 0 END) AS Dec,
            SUM(Records) AS YearlyTotal    
        FROM  vwjmrep
        WHERE datercvd >=@Year + '0101' and datercvd <= @Year + '1231'  AND Company IS NOT NULL 
        GROUP BY Company, Customer
Union All

SELECT @PreYear AS 'Year',Company, Customer,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '01' THEN Records ELSE 0 END) AS Jan,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '02' THEN Records ELSE 0 END) AS Feb,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '03' THEN Records ELSE 0 END) AS Mar,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '04' THEN Records ELSE 0 END) AS Apr,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '05' THEN Records ELSE 0 END) AS May,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '06' THEN Records ELSE 0 END) AS Jun,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '07' THEN Records ELSE 0 END) AS Jul,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '08' THEN Records ELSE 0 END) AS Aug,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '09' THEN Records ELSE 0 END) AS Sep,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '10' THEN Records ELSE 0 END) AS Oct,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '11' THEN Records ELSE 0 END) AS Nov,
            SUM(CASE WHEN SUBSTRING(DateRcvd,5,2) = '12' THEN Records ELSE 0 END) AS Dec,
            SUM(Records) AS YearlyTotal    
        FROM  vwjmrep
        WHERE datercvd >=@PreYear + '0101' and datercvd <= @PreYear + '1231'  AND Company IS NOT NULL 
        GROUP BY Company, Customer
)
,CTE1 as
(Select * from cte
union all
SELECT 
    @PreYear AS [Year],null,null,COALESCE(SUM(Jan),0),COALESCE(SUM(Feb),0),COALESCE(SUM(Mar),0),COALESCE(SUM(Apr),0),
    COALESCE(SUM(May),0),COALESCE(SUM(Jun),0),COALESCE(SUM(Jul),0),COALESCE(SUM(Aug),0),COALESCE(SUM(Sep),0),COALESCE(SUM(Oct),0),
    COALESCE(SUM(Nov),0),COALESCE(SUM(Dec),0),COALESCE((SUM(Jan) + SUM(Feb) + SUM(Mar) + SUM(Apr) + SUM(May) + SUM(Jun) + SUM(Jul) + SUM(Aug) + SUM(Sep) + SUM(Oct) + SUM(Nov) + SUM(Dec) ),0) AS YearlyTotal
FROM cte
WHERE [Year] = @PreYear
)
select * from cte1
ORDER BY 
    CASE WHEN Company is null THEN 1 ELSE 0 END,
    Company,[Year]
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/20628874

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档