mysql 按列显示_按客户分组并按月列枢轴查询显示的Mysql销...

假设您有以下表格：

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 |

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

您可以使用此查询执行此操作：

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;

并输出：

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

| 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 |

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