按客户分组并按月列透视查询显示的MySQL销售表 [英] Mysql sales table grouped by customer and displayed by month columns pivot query

问题描述

我有一个销售额表格，其中包含以下列:

I have a sales table with the following columns:

|  Customer_Id | amount |  date  |

最好的方法是按customer_id分组数据，并在不同的月份列上显示每个Customer_id的金额每月总计(SUM)(每个Customer_id一行)?

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?

所需的输出如下:

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

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

我相信在Sql中，这称为数据透视表.

I believe in Sql this is called a pivot 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;

输出:

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

这篇关于按客户分组并按月列透视查询显示的MySQL销售表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！