mysql - 如何对多行/列进行分组？

Question

我正在寻找一种方法来对 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 中这样做，因为它变得非常慢。非常感谢，已经!

Answer 1

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