我有一个名为“读数”的表，其中包含来自传感器的数据，其中包含以下各列：
id id_device date value
1 1 2015-01-01 00:00:00 0.2
2 2 2015-01-01 00:00:00 0.7
3 1 2015-01-01 00:00:10 0.3
4 2 2015-01-01 00:00:10 0.8
5 1 2015-01-01 00:00:20 0.4
6 2 2015-01-01 00:00:20 0.9
我想在一个查询中将其转换为该表：
date device_id_1 device_id_2
2015-01-01 00:00:00 0.2 0.7
2015-01-01 00:00:10 0.3 0.8
2015-01-01 00:00:20 0.4 0.9
我发现执行此操作称为“数据透视表”，但是我仅找到了如何对值求和，而不是如何按时间序列对其进行排序
我试过了
SELECT DISTINCT(date) FROM readings
然后在结果循环中，再次查询每个传感器
SELECT value FROM readings WHERE date=$date AND id_device=$id
但是我确信这不是很有效，因为它涉及很多查询（每个值一个查询）
sql查询将如何？
最佳答案
尝试这个：
SELECT r.id,
r.`date`,
GROUP_CONCAT(IF(r.device_id = 1,r.`VALUE`,NULL)) AS device_id_1,
GROUP_CONCAT(IF(r.device_id = 2,r.`VALUE`,NULL)) AS device_id_2
FROM readings r
GROUP BY r.`DATE`
ORDER BY r.`DATE` ASC;
+----+---------------------+-------------+-------------+
| id | date | device_id_1 | device_id_2 |
+----+---------------------+-------------+-------------+
| 1 | 2015-10-01 10:00:00 | 2 | 0.5 |
| 3 | 2015-10-01 10:01:00 | 3.1 | 7.5 |
+----+---------------------+-------------+-------------+
2 rows in set (0.00 sec)
这是动态device_id的版本
SELECT group_concat(
"GROUP_CONCAT(IF(device_id = ",
device_id,",`value`,NULL)) AS device_id_",
device_id
SEPARATOR ',\n') INTO @sql_mid
FROM (SELECT DISTINCT device_id FROM readings ORDER BY device_id) AS r
ORDER BY device_id;
SELECT CONCAT("SELECT id, `date`, ", @sql_mid, " FROM readings GROUP BY `DATE` ORDER BY `DATE` ASC") INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
它生成所有列
+----+---------------------+-------------+-------------+-------------+
| id | date | device_id_1 | device_id_2 | device_id_3 |
+----+---------------------+-------------+-------------+-------------+
| 1 | 2015-10-01 10:00:00 | 2 | 0.5 | NULL |
| 3 | 2015-10-01 10:01:00 | 3.1 | 7.5 | NULL |
| 5 | 2015-10-01 10:11:00 | NULL | NULL | 9.9 |
+----+---------------------+-------------+-------------+-------------+
3 rows in set (0.00 sec)
关于mysql - 旋转表格并显示n个有序的时间序列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33350524/