mysql - WITH ROLLUP 无法正常工作

Question

在这里，我在第一列中显示订单年份，在第二列中显示月份，在第三列中显示 Order_id。我想按月、按年和总计显示总计。

这是我想要得到的结果:

月总计、年总计和总计。

这是我的查询:

SELECT coalesce(extract(year FROM order_date), 'Grand Total') as 'Year'
    , CASE  when extract(year FROM order_date) is null then ' '
        when extract(month FROM order_date) is null then 'Year total'
        else extract(month FROM order_date)
    END as `Month`
    , CASE when extract(month FROM order_date) is null Then ' '
        else coalesce(oh.order_id, 'Month Total')
    END as 'Order ID'
    , sum(quantity * order_price) AS 'AmntDue'
    , sum(quantity) AS 'NumBooksPurch'
FROM a_bkorders.order_headers oh
JOIN a_bkorders.order_details od on oh.order_id = od.order_id
GROUP BY extract(year FROM order_date), extract(month FROM order_date), oh.order_id with rollup
;

但这是我得到的:

月总计似乎工作正常但年度总计或总计则不然。我不明白我做错了什么。

Answer 1

我不能保证这会奏效，因为我没有所有数据(也就是第二个表中的订单金额)。但我认为这样的事情会奏效。如果它搞砸了，请告诉我。

SELECT
    Year, 
    IF(Year = 'Grand Total', ' ', Month), 
    IF(Month = 'Year Total' OR Year = 'Grand Total', ' ', Ordered_ID),
    AmntDue, 
    NumberOfBooksPurch 
FROM(
    SELECT
        COALESCE(Year, 'Grand Total') AS Year,
        CASE 
            WHEN Year IS NULL THEN ' ' 
            ELSE COALESCE(MONTH, 'Year Total') 
        END AS Month,
        Ordered_ID,
        AmntDue, 
        NumberOfBooksPurch
    FROM (
        SELECT   
            YEAR(order_date) AS Year, 
            MONTH(order_date) AS Month,
            CASE 
                WHEN MONTH(order_date) IS NULL THEN ' '
                ELSE COALESCE(order_id, 'Month Total')
            END as Ordered_ID,
            SUM(quantity * order_price) AS AmntDue,
            COUNT(order_id) AS NumberOfBooksPurch
        FROM a_bkorders.order_headers
        JOIN a_bkorders.order_details USING (order_id)
        GROUP BY Year, Month, order_id WITH ROLLUP
    ) temp_table_alias
) final_temp_table;