mysql - Union在mysql中与group by一起使用时不起作用？怎么了？

Question

我写了一个查询，例如

(select meterID, timestamp from meter_data 
          where timestamp between 1369282639 AND 1369282699 
                AND deviceID = "1" 
                        GROUP BY meterID) 
UNION 

(select meterID, timestamp from meter_data 
          where timestamp between 1369282739 AND 1369282799 
                AND deviceID = "1" 
                         GROUP BY meterID);

我期望从中得到 2 组数据，例如 - 我的表中有 4 个不同的meterID，因此它应该返回 8 条记录，但它只返回 4 条记录

Answer 1

为了帮助理解数据，您可以尝试以下操作

SELECT a.meterID, MAX(b.timestamp), MAX(c.timestamp)
FROM meter_data a
LEFT OUTER JOIN meter_data b ON a.meterID = b.meterID AND b.timestamp between 1369282639 AND 1369282699 AND b.deviceID = "1" 
LEFT OUTER JOIN meter_data c ON a.meterID = c.meterID AND c.timestamp between 1369282739 AND 1369282799 AND c.deviceID = "1" 
GROUP BY a.meterID

这应该返回每个仪表 ID 一行以及该仪表 ID 每个范围内的最大时间戳。