我正在寻找一种在mysql中对多个列进行分组的方法。
该表几乎是:
Customer, Product, Date
Joe, Apple, 2011-01-01
Henry, Banana, 2011-05-26
Sally, Peach, 2011-06-02
Jane, Strawberry, 2010-06-25
我想做的是计算每个客户购买的产品数量,并在一年中按月进行分组。
所以看起来像。
COUNT(产品)|一月二月|三月....... |总
如果我仅使用GROUP BY产品,则MONTH(date)
Banana, January, 2
Banana, February, 3
Banana, March, 1
等等。
有没有办法做到这一点,以使我得到的行数与我拥有不同产品的数目一样,然后每个月都有一列?
我真的想稍后不要在PHP中执行此操作,因为它的运行速度非常慢。
非常感谢!
最佳答案
SELECT Product,
SUM(CASE WHEN MONTH(date) = 1 THEN 1 ELSE 0 END) Jan,
SUM(CASE WHEN MONTH(date) = 2 THEN 1 ELSE 0 END) Feb,
SUM(CASE WHEN MONTH(date) = 3 THEN 1 ELSE 0 END) Mar,
SUM(CASE WHEN MONTH(date) = 4 THEN 1 ELSE 0 END) Apr,
SUM(CASE WHEN MONTH(date) = 5 THEN 1 ELSE 0 END) May,
SUM(CASE WHEN MONTH(date) = 6 THEN 1 ELSE 0 END) Jun,
SUM(CASE WHEN MONTH(date) = 7 THEN 1 ELSE 0 END) Jul,
SUM(CASE WHEN MONTH(date) = 8 THEN 1 ELSE 0 END) Aug,
SUM(CASE WHEN MONTH(date) = 9 THEN 1 ELSE 0 END) Sep,
SUM(CASE WHEN MONTH(date) = 10 THEN 1 ELSE 0 END) Oct,
SUM(CASE WHEN MONTH(date) = 11 THEN 1 ELSE 0 END) Nov,
SUM(CASE WHEN MONTH(date) = 12 THEN 1 ELSE 0 END) Dec,
COUNT(date) Total
FROM
MyTable
GROUP BY
product