MySQL 2表联合

Question

我请求你的理解，因为我英语说得不好。

有四张桌子。

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中选择所有不同的时间值。然后，您可以将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

演示