MySQL基于其他列的Sum运算

Question

我有一个这样的表:

Date       | Name  | Pick  | Amount
-----------+-------+-------+-------
2018-01-01 | Alice | Apple |      2
2018-01-01 | Alice | Grape |      3
2018-01-01 | Bob   | Apple |      4
2018-01-02 | Alice | Apple |      5
2018-01-02 | Bob   | Grape |      6

生成如下结果的 SQL 语句是什么？

Name  | Apple | Grape | Total
------+-------+-------+------
Alice |     7 |     3 |    10
Bob   |     4 |     6 |    10

Answer 1

使用 CASE 条件，这将是可能的。

试试这个:

select name
    ,sum(case when pick = 'Apple' then amount end)Apple
    ,sum(case when pick = 'Grape' then amount end)Grape
    ,sum(case when pick = 'Apple' then amount end)
        +sum(case when pick = 'Grape' then amount end)Total
from your_table
group by name