我正在寻找一种在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

09-18 04:48
查看更多