我有一个包含以下内容的表格：
表1：MySQL表内容（表：“传感器”）
Date | Topic | Value
-------------------------------------------
2016/06/15 13:22:11 | pressure | 10.52
2016/06/15 13:22:11 | temperature | 55.25
2016/06/15 13:23:42 | temperature | 40.12
2016/06/15 13:25:10 | pressure | 8.10
2016/06/15 13:25:13 | temperature | 47.24
2016/06/15 13:27:30 | pressure | 11.47
2016/06/15 13:27:30 | temperature | 52.27
我想进行选择以检索此视图：
表2：来自表“传感器”的查询结果
Date | pressure | temperature
-------------------------------------------------
2016/06/15 13:22:11 | 10.52 | 55.25
2016/06/15 13:23:42 | | 40.12
2016/06/15 13:25:10 | 8.10 |
2016/06/15 13:25:13 | | 47.24
2016/06/15 13:27:30 | 11.57 | 52.27
我想从“传感器”表中选择DISTINCT Date，然后填充主题中的值（如果当时不存在，则该值为空）
PS。表“传感器”具有多个主题，但是我只需要“压力”和“温度”值即可构建图表。
谢谢
我认为应该工作
SELECT t1.Date as ReadingDate, Max(pressure) as Pressure, Max(temperature) as Temperature FROM
(
SELECT * FROM
(SELECT sensor.Date, "" AS pressure, sensor.value AS temperature
FROM sensor
WHERE sensor.topic="temperature") tbl_temp
UNION ALL
SELECT * FROM
(SELECT sensor.Date, sensor.value AS pressure, "" AS temperature
FROM sensor
WHERE sensor.topic="pressure") tbl_pressure
) AS t1
GROUP BY t1.Date
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句