MYSQL根据列字符串将值拆分为多列

Question

我有一个 table1，我正在努力获取输出如表2。有什么想法吗？

表1

+-----+-----------+------+----------+---------------------+
| id  | Sensor    | Temp | Humidity | DateTime            |
+-----+-----------+------+----------+---------------------+
|   1 | Sens1     |  9.7 |       55 | 2015-02-21 11:29:47 |
|   2 | Sens2     |  4.4 |       99 | 2015-02-21 11:45:00 |
|   3 | Sens1     |  9.8 |       56 | 2015-02-21 11:44:59 |
|   4 | Sens2     |  4.5 |       98 | 2015-02-21 11:59:24 |
+-----+-----------+------+----------+---------------------+

想要的输出:表2

+---------------------+-----------+----------+-----------+----------+
| Datetime            | Sens1temp | Sens1hum | Sens2temp | Sens2hum |
+---------------------+-----------+----------+-----------+----------+
| 2015-02-21 11:29:47 |      9.7  |       55 |    null   |    null  |
| 2015-02-21 11:45:00 |     null  |     null |     4.4   |      99  |
| 2015-02-21 11:44:59 |      9.8  |       56 |    null   |    null  |
| 2015-02-21 11:59:24 |     null  |     null |     4.5   |      98  |
+---------------------+-----------+----------+----------+-----------+

我花了几个小时进行搜索，但就是无法让它发挥作用。

Answer 1

试试这个:

SELECT Datetime,
       CASE WHEN Sensor='Sens1' THEN Temp END AS Sens1temp,
       CASE WHEN Sensor='Sens1' THEN Humidity END AS Sens1hum,
       CASE WHEN Sensor='Sens2' THEN Temp END AS Sens2temp,
       CASE WHEN Sensor='Sens2' THEN Humidity END AS Sens2hum
FROM TableName
GROUP BY Datetime

SQL Fiddle 中的示例结果.