MySQL - 将来自同一个表的多个选择组合成一个结果表与一个组

Question

我有一个仪表读数表，其中包含 meterNo、readingValue 和 readingDate。

我想显示每年每个 meterNo 的 readingValue 的总和。

例如:

SELECT meterNo, SUM(readingValue) '2009' FROM readings
WHERE readingDate >= '2009-01-01' AND readingDate <= '2009-12-31'
GROUP BY meterNo;

将显示表格:

   meterNo  2009
   --------------
     4       50
     5       5
     12      30
     13      63
     18      18
     26      484
     27      21
     28      510
     29      28

这就是我想要的，它显示了 2009 年每个仪表的总读数。但是，我需要结果表来显示每一年的列。所以它会是这样的:

   meterNo  2009  2010  2011  2012
   --------------------------------
     1             20    35     50
     2                   45     35
     3                   50     60
     4       50    35    20     30
     5       5     10    10     15
     6                          30

等等……

我可以重用查询的 where 部分:

WHERE readingDate >= '2009-01-01' AND readingDate <= '2009-12-31'

只需更改每年的日期，但是如何在一个结果表中将每个 WHERE 结果显示为它自己的列？

Answer 1

MySQL 没有 PIVOT 函数，但您可以使用带有 CASE 表达式的聚合函数将数据行转换为列。

如果您的年数有限，那么您可以对查询进行硬编码:

select meterNo,
  sum(case when year(readingDate) = 2009 then readingValue else 0 end) `2009`,
  sum(case when year(readingDate) = 2010 then readingValue else 0 end) `2010`,
  sum(case when year(readingDate) = 2011 then readingValue else 0 end) `2011`,
  sum(case when year(readingDate) = 2012 then readingValue else 0 end) `2012`,
  sum(case when year(readingDate) = 2013 then readingValue else 0 end) `2013`
from readings
group by meterno;

参见 SQL Fiddle with Demo

但是，如果您要有未知数量的值或在新的年份添加到数据库时要调整的查询，那么您可以使用准备好的语句来生成动态 SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN year(readingDate) = ',
      year(readingDate),
      ' THEN readingValue else 0 END) AS `',
      year(readingDate), '`'
    )
  ) INTO @sql
FROM readings;

SET @sql 
  = CONCAT('SELECT meterno, ', @sql, ' 
            from readings
            group by meterno');

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

参见 SQL Fiddle with Demo .两者都给出结果:

| METERNO | 2009 | 2010 | 2012 | 2013 | 2011 |
----------------------------------------------
|       1 |   90 |  180 |    0 |   90 |   90 |
|       2 |   50 |    0 |   90 |    0 |    0 |
|       3 |   80 |   40 |   90 |   90 |    0 |

作为旁注，如果您希望 null 在没有值而不是零的行中显示，那么您可以删除 else 0(参见 Demo )