mysql - 自定义 ROLLUP 行 mySQL

Question

我想总结销售数据，并想在最后一行求和，我使用“GROUP BY”和“WITH ROLLUP”，但结果是:

+--------+--------------------+------------+--------+-----------+
|   id   |        name        |    date    | amount |   total   |
+--------+--------------------+------------+--------+-----------+
| Z00015 | Mebel Harmonis     | 2019-05-09 |      2 |  10000000 |
| Z00016 | Mebel Harmonis     | 2019-05-09 |     10 |  45000000 |
| Z00017 | Mebel Tunggal Jaya | 2019-05-10 |      3 |  12000000 |
| (null) | Mebel Tunggal Jaya | 2019-05-10 |     29 | 131000000 |
+--------+--------------------+------------+--------+-----------+

我想要的最后一行:

+--------+--------+--------+----+-----------+   
| (null) | (null) | (null) | 29 | 131000000 |
+--------+--------+--------+----+-----------+

这是我的查询:

SELECT
    order2.id_order AS id,
    customer.name_customer AS name,
    DATE( order2.date_order ) AS date ,
    Count( order_detail.id_detail ) AS amount,
    SUM( harga ) AS total 
FROM
    order_detail
    INNER JOIN order2 ON order2.id_order = order_detail.id_order
    INNER JOIN customer ON order2.id_customer = customer.id_customer
    INNER JOIN produk ON produk.id_produk = order_detail.id_produk
    INNER JOIN sofa ON sofa.id_sofa = produk.id_sofa 
WHERE
    date( date_order ) >= '2019-05-01' 
    AND date( date_order ) <= '2019-05-31' 
GROUP BY
    order2.id_order WITH ROLLUP;

Answer 1

您需要在 GROUP BY 子句中指定可以组合在一起以获得总计的所有列:

GROUP BY id, name, date WITH ROLLUP

但是，这将为每个 id 和 id, name 创建中间小计。您可以使用以下方法过滤掉它们:

HAVING id IS NOT NULL OR (id IS NULL AND name IS NULL AND date IS NULL)