MySQL 2 个表 UNION

Question

因为我不会说英语，所以请您理解。

有 4 个表。

sensor (PK: sensor)
sensor | service
   1   |    1
   2   |    2

equipment (PK: id, FK: sensor)
id | equip | sensor | 
 1 |   8   |    1   |
 2 |   8   |    1   |
 3 |   8   |    2   |
 4 |   7   |    2   |

A (PK: AUTO INCREMENT, UNIQUE: id, time, FK: id)
id |   time | temperature
 1 |  1027  | 30
 1 |  1028  | 30
 1 |  1029  | NULL
 1 |  1030  | 60
 1 |  1101  | 999

B (PK: AUTO INCREMENT, UNIQUE: id, time, FK: id)
id |   time | temperature
 2 | 1027   | 40
 2 | 1029   | 50
 2 | 1030   | NULL
 2 | 1031   | 59

我想要以下结果。

time | A_temperature | B_temperature
1027 | 30            | 40
1028 | 30            | NULL
1029 | NULL          | 50
1030 | 60            | NULL
1031 | NULL          | 59

所以我做了以下查询:

SELECT DISTINCT COALESCE(A.time, B.time) time, A.temperature AS A_temperature, B.temperature AS B_temperature
FROM equipment AS equip
JOIN sensor sen
    ON  sen.sensor = equip.sensor
   AND sen.service = 1
JOIN A
    ON A.id = equip.id
    AND (A.time>= '1027' AND A.time<= '1031')  
LEFT JOIN B
   ON B.id = equip.id
   AND (B.time>= '1027' AND B.time<= '1031')
   AND B.time= A.time
WHERE equip.equip = 8

UNION

SELECT DISTINCT COALESCE(B.time, A.time) time, A.temperature AS A_temperature, B.temperature AS B_temperature
FROM equipment AS equip
JOIN sensor sen
    ON sen.sensor = equip.sensor
   AND sen.service = 1
LEFT JOIN B
   ON B.id = equip.id
   AND (B.time>= '1027' AND B.time<= '1031')
   AND B.time= A.time
JOIN A
   ON A.id = equip.id
   AND (A.time>= '1027' AND A.time<= '1031')  
WHERE equip.equip = 8
ORDER BY time ASC;

但是我没有得到我想要的结果。

time | A_temperature | B_temprature
1027 |      30       |    NULL
1027 |     NULL      |     40
1028 |      30       |    NULL
1028 |     NULL      |    NULL
1029 |     NULL      |     50
 ....

执行上述查询后，将A.time和B.time分开，输出结果。我想同时结合这些。如果时间是null，我们想把它放在非空时间。

Answer 1

我认为您可以通过创建一个子查询来简化上一个问题的答案，该子查询仅从表 A 和 B 中选择所有不同的时间值。然后你可以分别 JOIN A 和 B 表和 GROUP BY 时间值，使用 MAX 聚合温度，因为值将有效或 NULL，MAX 将忽略:

SELECT t.time, MAX(A.temperature) AS A_temperature, MAX(B.temperature) AS B_temperature
FROM equipment e
JOIN sensor s ON s.sensor = e.sensor AND s.service = 1
JOIN (SELECT time FROM A UNION SELECT time FROM B) t
LEFT JOIN A on A.id = e.id AND A.time = t.time
LEFT JOIN B on B.id = e.id AND B.time = t.time
WHERE t.time BETWEEN 1027 AND 1031
GROUP BY t.time
ORDER BY t.time

输出:

time    A_temperature   B_temperature
1027    30              40
1028    30  
1029                    50
1030    60  
1031                    59

Demo