在同一時間對兩個不同表格的結果進行分組

我有兩個簡單的表：

室內的

id | timestamp | temp | humi

戶外

id | timestamp | temp

還有兩個選擇，它們給了我過去 24 小時內按同一小時分組的時間和平均溫度：

SELECT DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;

SELECT DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;

現在我需要做的是將這兩個結果按同一小時分組，考慮到整個小時室內或室外表中沒有記錄的可能性，所以我需要得到：

time | avg_out_temp | avg_in_temp
11:00 | 12.5 | 21.4
12:00 | 13.9 | null
13:00 | null | 22.4
14:00 | 14.0 | 22.5

我正在使用 MariaDB：

mysql  Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

因為您的兩個查詢都返回相同數量（和類型）的欄位，所以可以與UNION ALL. 在ORDER BY查詢結束時只需要一次。

indoor要查看和這之間的區別，outdoor可以將其添加到您的查詢中。

SELECT 'out' as door,DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
UNION ALL
SELECT 'in' as door,DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;

Mysql沒有full out join，這就是為什麼必須模擬它的原因。所以 iz os 退出醜陋。

SELECT IFNULL(ti.time,tou.time) time,ti.avg_in_temp,tou.avg_out_temp
FROM 
((SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon
,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time
, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE `timestamp` >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m-%d %H')
) ti LEFT JOIN 
(SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon 
,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time
, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE `timestamp` >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m-%d %H')
) tou on ti.timecon = tou.timecon)
union
SELECT IFNULL(ti.time,tou.time) time,ti.avg_in_temp,tou.avg_out_temp
FROM 
((SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon
,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time
, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE `timestamp` >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m-%d %H')
) ti RIGHT JOIN 
(SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon 
,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time
, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE `timestamp` >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m-%d %H')
) tou on ti.timecon = tou.timecon)
ORDER BY time ASC;

我從室內和室外的 Select 中刪除了排序，因為它不需要。 在這裡你可以找到一個例子

當然，由於你的 where 子句，你必須有不早於一天的時間戳，所以 select 查詢不會在 exqamle 中返回任何內容

引用自：https://dba.stackexchange.com/questions/248816