MySQL，如何按产品和按月求和

Question

我有如下表格

 Date        |  Product      |  Qty
-------------|---------------|------
12-Dec-12    | reference1    | 1
14-Dec-12    | reference2    | 2
14-Dec-12    | reference1    | 3
1-Jan-13     | reference2    | 4
3-Jan-13     | reference2    | 5
3-Jan-13     | reference3    | 6

我想通过查询得到它

Product    | Dec 2012   | Jan 2013
===========|============|========== 
reference1 |    4       | 0
reference2 |    2       | 9
reference3 |    0       | 6

我已经知道如何分组了，我的问题是如何拥有动态列(我希望能够选择过去 6 个月、12 个月 或 24 个月).

Answer 1

您正在尝试将数据从行旋转到列。 MySQL 没有数据透视函数，但您可以使用带有 CASE 的聚合函数来获取结果:

select product,
  sum(case when month(date) = 12 and year(date) = 2012 
           then qty else 0 end) Dec2012,
  sum(case when month(date) = 1 and year(date) = 2013 
           then qty else 0 end) Jan2013
from yourtable
group by product

参见 SQL Fiddle with Demo .

这也可以使用子查询来编写，以获取月-年格式的日期:

select product,
  sum(case when MonthYear = 'Dec_2012' then qty else 0 end) Dec2012,
  sum(case when MonthYear = 'Jan_2013' then qty else 0 end) Jan2013
from
(
  select product,
    date_format(date, '%b_%Y') MonthYear,
    qty
  from yourtable
) src
group by product;

参见 SQL Fiddle with Demo .

然后，如果您想动态生成一个日期列表，或者想要返回未知数量的日期，您可以使用准备好的语句来生成动态 SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when MonthYear = ''',
      MonthYear,
      ''' then qty else 0 end) AS ',
      MonthYear
    )
  ) INTO @sql
FROM 
(
  select product,
    date_format(date, '%b_%Y') MonthYear,
    qty
  from yourtable
) src;

SET @sql = CONCAT('SELECT product, ', @sql, ' 
                   from
                   (
                    select product,
                      date_format(date, ''%b_%Y'') MonthYear,
                      qty
                    from yourtable
                   ) src
                   GROUP BY product');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见 SQL Fiddle with Demo .这三个都会给你结果:

|    PRODUCT | DEC_2012 | JAN_2013 |
------------------------------------
| reference1 |        4 |        0 |
| reference2 |        2 |        9 |
| reference3 |        0 |        6 |