mysql - 如何使用 MySQL 获取类似结果的数据透视表

Question

假设我们在 mysql:test 中有一个表；
如何获得像excel中的数据透视表这样的结果表？

尝试使用“合并”，但 mysql 不支持它。

尝试使用“联合”，但增加的是索引而不是列。

select * from test;

输出

+----+------------+------+-------+
| id | date       | name | value |
+----+------------+------+-------+
|  1 | 2019-01-01 | a    |     1 |
|  2 | 2019-01-02 | a    |     2 |
|  3 | 2019-01-03 | a    |     3 |
|  4 | 2019-01-04 | a    |     4 |
|  5 | 2019-01-05 | a    |     5 |
|  6 | 2019-01-06 | a    |     6 |
|  7 | 2019-01-03 | b    |     9 |
|  8 | 2019-01-04 | b    |     9 |
|  9 | 2019-01-05 | b    |     8 |
| 10 | 2019-01-06 | b    |     7 |
| 11 | 2019-01-05 | c    |     5 |
| 12 | 2019-01-06 | c    |     6 |
| 13 | 2019-01-07 | c    |     7 |
| 14 | 2019-01-08 | c    |     8 |
| 15 | 2019-01-09 | c    |     9 |
+----+------------+------+-------+

想要得到:

+------------+---+---+---+
| date       | a | b | c |
+------------+---+---+---+
| 2019-01-01 | 1 |   |   |
| 2019-01-02 | 2 |   |   |
| 2019-01-03 | 3 | 9 |   |
| 2019-01-04 | 4 | 9 |   |
| 2019-01-05 | 5 | 8 | 5 |
| 2019-01-06 | 6 | 7 | 6 |
| 2019-01-07 |   |   | 7 |
| 2019-01-08 |   |   | 8 |
| 2019-01-09 |   |   | 9 |
+------------+---+---+---+

我知道在Excel中很容易得到结果，但是如何使用SQL呢？

编辑:

后续问题。

如果我不知道“名称”列中的名称是什么怎么办？

也就是说，可能有a,b,c,d,e,f,g...但我不知道有多少不同的名字。

如何列出所有结果？ ()

+------------+---+---+---+
| date       | a | b | c |...... and so on.
+------------+---+---+---+

Answer 1

您可以将 CASE 与 GROUP_CONCAT 一起使用以获得所需的输出。好的做法是避免保留字，如 name、date、value

SELECT date,
GROUP_CONCAT(CASE WHEN name='a' THEN value END) as 'a', 
GROUP_CONCAT(CASE WHEN name='b' THEN value END) as 'b', 
GROUP_CONCAT(CASE WHEN name='c' THEN value END) as 'c' 
FROM test GROUP BY `date`;

编辑:这可以通过使用 prepared statement 创建动态查询来实现。

由于 value 是针对特定日期的列求和的，因此与 SUM 共享查询。如果需要，您可以替换为 GROUP_CONCAT。

SELECT 
GROUP_CONCAT(CONCAT(" SUM(CASE WHEN name='",name,"' THEN value END) AS '",name,"'")) 
INTO @pivotcol
FROM (SELECT `name` FROM test GROUP BY `name`) t;

SET @pivotquery =CONCAT("SELECT date, ",@pivotcol," from test GROUP BY `date`");

PREPARE stmt FROM @pivotquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;