mysql - 如何对另一列的 DISTINCT 值和 GROUP BY 日期求和？

Question

我们如何才能仅在同一日期对每个事件求和金额并为每个日期输出一行？该查询不起作用。

SELECT SUM(amount), type, date FROM table GROUP BY DISTINCT date;

表格

+----+------------+-----------+---------+
| id |    date    | activity  | amount  |
+----+------------+-----------+---------+
| 1  | 2017-12-21 | Shopping  | 200     |
| 2  | 2017-12-21 | Gift      | 240     |
| 3  | 2017-12-23 | Give Away | 40      |
| 4  | 2017-12-24 | Shopping  | 150     |
| 5  | 2017-12-25 | Give Away | 120     |
| 6  | 2017-12-25 | Shopping  | 50      |
| 7  | 2017-12-25 | Shopping  | 500     |
+----+------------+-----------+---------+

所需结果

+------------+-----------+------+-----------+
|    date    | Shopping  | Gift | Give Away |
+------------+-----------+------+-----------+
| 2017-12-21 | 200       | 240  |           |
| 2017-12-23 |           |      | 40        |
| 2017-12-24 | 150       |      |           |
| 2017-12-25 | 550       |      | 120       |
+------------+-----------+------+-----------+

Answer 1

用途:

select `date`, 
  sum(if (activity='Shopping', amount, null)) as 'Shopping',
  sum(if (activity='Gift', amount, null)) as 'Gift',
  sum(if (activity='Give Away', amount, null)) as 'Give Away'
from table
group by `date`