我有以这种格式存储数据的MySQL表
Type|Month|Count
----+-----+-------
1 |9 |4
3 |9 |7
99 |9 |2
1 |10 |6
3 |10 |7
99 |10 |9
.......
类型列可容纳3个值1,3,99中的任何一个.
一个月的值将介于1到12之间.
计数可以是随机的.
我想要的输出是这样的:
Month|Type1|Type3|Type99
-----+-----+-----+-------
9 |4 |7 |2
10 |6 |7 |9
................
我遇到了this演示,但对此并不太了解.
这是带有演示数据的样本fiddle.
任何帮助表示赞赏.
解决方法:
在下面的查询中尝试,您所需要的被称为MYSQL枢轴,下面的查询解决了您的问题
静态方式
SELECT Month,
SUM(CASE WHEN Type = 1 THEN 'count' ELSE 0 END) AS Type1,
SUM(CASE WHEN Type = 3 THEN 'count' ELSE 0 END) AS Type3,
SUM(CASE WHEN Type = 99 THEN 'count' ELSE 0 END) AS Type99
FROM my_table
GROUP BY Month
动态方式
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(CASE WHEN Type= ',
Type, ' THEN count ELSE 0 END) AS '
, 'Type', Type))
INTO @sql
FROM
my_table;
SET @sql = CONCAT('SELECT Month, ', @sql, '
FROM my_table
GROUP BY Month');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
标签:mysql
来源: https://codeday.me/bug/20191121/2049704.html