将两个 T-SQL 数据透视查询合并为一个

Question

假设您有这个表：

CREATE TABLE Records
(
    RecordId       int IDENTITY(1,1) NOT NULL,
    CreateDate     datetime          NOT NULL,
    IsSpecial      bit               NOT NULL
    CONSTRAINT PK_Records   PRIMARY KEY(RecordId)
)

现在需要创建一个报告，其中总记录和总特殊记录按月细分。我可以单独使用这两个查询：

--      TOTAL RECORDS PER MONTH
SELECT January, February, March, April, May, June,
    July, August, September, October, November, December
FROM (
    SELECT RecordId, DATENAME(MONTH, CreateDate) AS RecordMonth
    FROM dbo.Records
) AS SourceTable
PIVOT (
    COUNT(RecordId) FOR RecordMonth IN (January, February, March, April, May, June,
    July, August, September, October, November, December)
) AS PivotTable;

--      TOTAL SPECIAL RECORDS PER MONTH
SELECT January, February, March, April, May, June,
    July, August, September, October, November, December
FROM (
    SELECT RecordId, DATENAME(MONTH, CreateDate) AS RecordMonth
    FROM dbo.Records
    WHERE IsSpecial = 1
) AS SourceTable
PIVOT (
    COUNT(RecordId) FOR RecordMonth IN (January, February, March, April, May, June,
    July, August, September, October, November, December)
) AS PivotTable;

结果可能如下所示：

                  Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec
total              0     0    2     2     1      0     0    1      2     1     2    4
total special      0     0    1     0     1      0     0    0      0     0     0    2 

是否可以将这两个查询组合成一个更有效的查询？

Answer 1

我会这样做：

SELECT
     CASE SQ.total_type
          WHEN 1 THEN 'total special'
          WHEN 2 THEN 'total expensive'
          ELSE 'total'
     END AS total_type,
     SUM(CASE WHEN MONTH(R.CreateDate) = 1 THEN 1 ELSE 0 END) AS January,
     SUM(CASE WHEN MONTH(R.CreateDate) = 2 THEN 1 ELSE 0 END) AS February,
     SUM(CASE WHEN MONTH(R.CreateDate) = 3 THEN 1 ELSE 0 END) AS March,
     ...
FROM
     dbo.Records R
INNER JOIN
     (
          SELECT 0 AS total_type UNION ALL   -- All
          SELECT 1 UNION ALL                 -- IsSpecial
          SELECT 2                           -- IsExpensive
     ) AS SQ ON
     (R.IsSpecial | (R.IsExpensive * 2)) & SQ.total_type = SQ.total_type
GROUP BY
     SQ.total_type
ORDER BY
     SQ.total_type DESC

Answer 2

每个数据透视表只能有一个聚合 (COUNT(RecordId))，因此您所做的就是使用 UNION ALL 合并为一个结果集，并使用合适的额外列来标识每个数据透视表。

否则，您无法区分数据透视表中的 2 个不同聚合

Answer 3

感谢汤姆提供的解决方案，它回答了我的关键问题。

对我来说太糟糕了，我问错了问题。对于我的问题，我现在觉得最好使用像这样的简单分组查询：

SELECT DATENAME(MONTH, CreateDate) AS Month,
    COUNT(*) AS Total,
    SUM(CASE
        WHEN IsSpecial = 1 THEN 1
        ELSE 0
    END) AS TotalSpecial,
    SUM(CASE
        WHEN IsExpensive = 1 THEN 1
        ELSE 0
    END) AS TotalExpensive
FROM Records
GROUP BY DATENAME(MONTH, CreateDate);

然后剩下要做的就是在显示结果之前旋转结果。很高兴认识是吗？