mysql - 以 'Totals' 作为最终列的 SQL 语句

Question

我正在尝试运行第一列为“月”的 SQL 查询。每列都有一个数值(或月份编号)，最后一列将显示“总计”。问题是，当我尝试对它们进行排序时，每次到达“总计”列时都会出现错误，因为它正在寻找 INT 值。例如，我有这样的查询:

SELECT CY.Month, CY.PaymentAmount_ThisYear, LY.PaymentAmount_LastYear FROM
(SELECT DATEPART(MM, Orders.OrderDate) AS Month, SUM(Orders.PaymentAmount) as PaymentAmount_ThisYear 
    FROM Orders WHERE DATEPART(YY, Orders.OrderDate) = DATEPART(YY, DATEADD(MM, -1, CURRENT_TIMESTAMP))
    GROUP BY DATEPART(MM, Orders.OrderDate)
) CY
INNER JOIN 
(SELECT DATEPART(MM, Orders.OrderDate) AS Month, SUM(Orders.PaymentAmount) as PaymentAmount_LastYear
    FROM Orders WHERE DATEPART(YY, Orders.OrderDate) = DATEPART(YY, DATEADD(MM, -13, CURRENT_TIMESTAMP))
    GROUP BY DATEPART(MM, Orders.OrderDate)
) LY
ON CY.Month = LY.Month
UNION
SELECT 'Totals' AS Month, CY.PaymentAmount_ThisYear, LY.PaymentAmount_LastYear FROM
(SELECT SUM(Orders.PaymentAmount) AS PaymentAmount_ThisYear
    FROM Orders WHERE DATEPART(YY, Orders.OrderDate) = DATEPART(YY, DATEADD(MM, -1, CURRENT_TIMESTAMP))
) CY,
(SELECT SUM(Orders.PaymentAmount) AS PaymentAmount_LastYear
    FROM Orders WHERE DATEPART(YY, Orders.OrderDate) = DATEPART(YY, DATEADD(MM, -13, CURRENT_TIMESTAMP))
) LY

如果我将“总计”更改为 13，它会起作用并将总计放在底部，但该行显示“13”而不是“总计”。

然后我尝试的是在查询的一开始我改变了这个:

SELECT CY.Month

至

SELECT CAST(CY.Month AS VARCHAR(10)) AS Month

但是当我这样做时，它命令它们是错误的:

1
10
11
5

因为它将它们识别为 varchar 值。我基本上是在寻找这个以数字方式对月份进行排序，但最后一列显示“总计”。

我也尝试过做一个

订购依据CASE ISNUMERIC(月)WHEN 1 THEN CAST(月份为 INT)其他月份结束

但这给了我一个“不明确的列名‘月份’。”错误。

我有点卡住了，有什么想法吗？谢谢!

Answer 1

我认为您可以大大简化查询并使用 GROUP BY 的 ROLLUP 选项来进行总计，例如:

SELECT 
      CASE 
        WHEN GROUPING(MONTH(orders.orderdate)) = 1 
        THEN 'Totals' 
        ELSE CONVERT(CHAR(2), MONTH(orders.orderdate)) 
      END AS [Month]
    , SUM(
        CASE 
            WHEN YEAR(orders.orderdate) = Datepart(yy, Dateadd(mm, -1, CURRENT_TIMESTAMP)) 
            THEN orders.paymentamount 
            ELSE 0.00
        END
        )   AS PaymentAmount_ThisYear 
    , SUM(
        CASE 
            WHEN YEAR(orders.orderdate) = Datepart(yy, Dateadd(mm, -13, CURRENT_TIMESTAMP)) 
            THEN orders.paymentamount 
            ELSE 0.00 
        END
        )   AS PaymentAmount_LastYear 
FROM   
    orders 
GROUP BY   
    MONTH(orders.orderdate)
WITH ROLLUP