具有多个组的 Mysql Rollup

Question

Mytable，其 ID 作为自动增量客户、日期和金额我想做一个汇总，将每个客户每天的金额相加我的意思是汇总按日期和客户分组我的结果应该是这样的:

+---------+--------------+--------------+------------+
| Id      | Date         | Customer     |     Amount |  
+---------+--------------+--------------+------------+
| 1       | 2017-09-19   |          B   |        10  | 
| 4       | 2017-09-19   |          B   |        15  | 
| 8       | 2017-09-19   |          B   |        02  | 
| 6       | 2017-09-19   |          B   |        18  |
| 5       | 2017-09-19   |          B   |        05  | 
| Total   | NULL         |          B   |        50  | 

| 9       | 2017-09-19   |         C    |        11  | 
|14       | 2017-09-19   |         C    |        10  |
| 12      | 2017-09-19   |         C    |        09  | 
| Total   | NULL         |         C    |        30  | 

| 11       | 2017-09-18  |         B    |         20 | 
| 15       | 2017-09-18  |         B    |         40 |
| Total    | NULL        |         B    |         60 | 

| 10       | 2017-09-18  |         A    |         1  | 
| 13       | 2017-09-18  |         A    |         1  | 
| 16       | 2017-09-18  |         A    |         1  | 
| 7        | 2017-09-18  |         A    |         1  |
| 3        | 2017-09-18  |         A    |         1  | 
| Total    | NULL        |         A    |         5  | 

| 2        | 2017-09-18   |         C   |        90  | 
| Total    | NULL         |         C   |        90  | 

我尝试过:

Select
Mytable.Id,
Mytable. Date,
Mytable.Customer,
Mytable.Amount
From Mytable
GROUP BY Mytable. Date, Mytable.Customer WITH ROLLUP

但这并没有按预期工作。请帮忙

Answer 1

您需要一个聚合。这符合你的要求吗？

select t.Date, t.Customer,
       sum(t.Amount) as Amount
from Mytable t
group by t.Date, t.Customer with rollup;

如果您确实想要 id，那么:

select t.id, t.Date, t.Customer,
       sum(t.Amount) as Amount
from Mytable t
group by t.id, t.Date, t.Customer with rollup;