mysql - 两个 where "date"子句有不同的列

Question

我想在 mysql 中有 2 个不同日期的不同列

比如我有这张表

id     name     amount     date

1      Jane     20.00      2015-08-14
2      Joe      10.00      2015-08-15
3      Joe      20.00      2015-08-21
4      Jane     30.00      2015-09-21
5      Joe      20.00      2015-09-25
6      Jane     10.00      2015-09-25

我想得到总金额 groupby名称并显示不同日期的两列，其中一个是 where date <= '2015-08-31'另一个是 where date <= '2015-09-30'

我想要的输出是

id    name    amount_aug    amount_sep
1     Jane    20.00         60.00
2     Joe     30.00         50.00

到目前为止，我的查询是 select name, SUM(amount) amount_sum from table_name groupby name这将简单地输出

name     amount_sum
Jane     60.00
Joe      50.00

Answer 1

SELECT
  name,
  SUM(
    IF( date <= '2015-08-31', amount, 0 )
  ) amount_aug,
  SUM(
    IF( date <= '2015-09-30', amount, 0 )
  ) amount_sep
FROM table_name
GROUP BY name