MySQL合并不同WHERE条件的查询 实现温湿度数据行转列展示

解决方案 #

你可以使用条件聚合的方式实现行转列，不需要多次关联表，执行效率更高，SQL语句如下：

SELECT 
  DEVICE AS Room,
  MAX(CASE WHEN READING = 'temperature' THEN VALUE END) AS Temperature,
  MAX(CASE WHEN READING = 'humidity' THEN VALUE END) AS Humidity
FROM `current`
GROUP BY DEVICE
ORDER BY DEVICE;

逻辑说明 #

• 首先通过GROUP BY DEVICE按房间分组，每个房间最终只会返回一行结果
• 用CASE WHEN对不同的READING类型做判断，匹配到对应读数类型就返回对应数值，否则返回NULL
• 用聚合函数MAX()（也可以替换为MIN()，因为每个房间单个读数类型只会有一条有效值，聚合函数会自动忽略NULL值）把对应值提取为单独的列
• 如果需要严格保留你原有语句的LIKE匹配逻辑，把语句中的=替换为LIKE即可

可选方案：子查询JOIN关联 #

如果你希望基于你原本的两条查询语句直接合并，也可以用关联查询实现：

SELECT t.Room, t.Temperature, h.Humidity
FROM (
  SELECT DEVICE as Room, VALUE as Temperature FROM `current` WHERE `READING` LIKE 'temperature'
) t
INNER JOIN (
  SELECT DEVICE as Room, VALUE as Humidity FROM `current` WHERE `READING` LIKE 'humidity'
) h ON t.Room = h.Room
ORDER BY t.Room;

如果存在某个房间只有温度或只有湿度的场景，把INNER JOIN替换为LEFT JOIN即可保留缺少其中一项读数的房间数据。

内容的提问来源于stack exchange，提问作者ps915