mysql 顾客表,按客户分组并按月列透视查询显示的MySQL销售表

I have a sales table with the following columns:

| Customer_Id | amount | date |

What would be the best way to group data by customer_id, and displaying monthly total(SUM) of amount for each Customer_id (one row per Customer_id), on distinct month columns?

The desired output would be something like:

Customer |January | February | March | ....

Customer_id |SUM amount | SUM amount | SUM amount | ....

I believe in Sql this is called a pivot table.

¡Thanks!

解决方案

Let's say you have following table:

mysql> select * from sales;

+-------------+--------+------------+

| customer_id | amount | date |

+-------------+--------+------------+

| 1 | 12 | 2015-01-01 |

| 1 | 1 | 2015-01-02 |

| 1 | 663 | 2015-02-12 |

| 2 | 22 | 2015-01-03 |

| 2 | 21 | 2015-02-12 |

| 2 | 11 | 2015-02-12 |

| 2 | 9 | 2015-04-12 |

+-------------+--------+------------+

You can do this using this query:

SELECT

customer_id,

sum(if(month(date) = 1, amount, 0)) AS Jan,

sum(if(month(date) = 2, amount, 0)) AS Feb,

sum(if(month(date) = 3, amount, 0)) AS Mar,

sum(if(month(date) = 4, amount, 0)) AS Apr,

sum(if(month(date) = 5, amount, 0)) AS May,

sum(if(month(date) = 6, amount, 0)) AS Jun,

sum(if(month(date) = 7, amount, 0)) AS Jul,

sum(if(month(date) = 8, amount, 0)) AS Aug,

sum(if(month(date) = 9, amount, 0)) AS Sep,

sum(if(month(date) = 10, amount, 0)) AS Oct,

sum(if(month(date) = 11, amount, 0)) AS Nov,

sum(if(month(date) = 12, amount, 0)) AS `Dec`

FROM sales

GROUP BY customer_id;

And the output:

+-------------+------+------+------+------+------+------+------+------+------+------+------+------+

| customer_id | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |

+-------------+------+------+------+------+------+------+------+------+------+------+------+------+

| 1 | 13 | 663 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| 2 | 22 | 32 | 0 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

+-------------+------+------+------+------+------+------+------+------+------+------+------+------+