MySQL查询返回多个求和列

Question

我的 SQL 表中有以下数据。

uid    user    category    count    date
--------------------------------------------------
1      henry   RED         4        2013-05-01
2      john    BLUE        3        2013-05-01
3      alice   RED         5        2013-05-01
4      eric    GREEN       2        2013-05-02
5      eric    BLUE        2        2013-05-02
6      john    RED         3        2013-05-02
7      henry   GREEN       2        2013-05-02
8      eric    RED         3        2013-05-03
9      john    BLUE        5        2013-05-03

我想要一个返回以下数据的查询

category    2013-05-01    2013-05-02    2013-05-03
---------------------------------------------------
RED         9             3             3
BLUE        3             2             5
GREEN       0             4             0

我可以通过以下查询获取一列数据，但我不知道如何将多列分组，如上所示。我希望我的 PHP 呈现一个查询，而不是循环遍历每个日期。

SELECT category, SUM(count) 
FROM dbtable 
WHERE date='2013-05-01' 
GROUP BY category;

这可能吗？想法？

Answer 1

像这样的东西应该可以工作:

SELECT
  category,
  SUM(CASE WHEN date = '2013-05-01' THEN count END) AS `2013-05-01`,
  SUM(CASE WHEN date = '2013-05-02' THEN count END) AS `2013-05-02`,
  SUM(CASE WHEN date = '2013-05-03' THEN count END) AS `2013-05-03`
FROM dbtable
GROUP BY category