mysql 记录一次查询的分组的问题

需求说明

mysql查询分组中，排出最后一条数据，其他数据的某个字段修改某个值。

最终结果

查询需要处理的数据

SELECT * from BI_STATISTICS.device_offline_log
WHERE
ISNULL( onlineTime )
AND id NOT IN (
SELECT a.id FROM (
SELECT max( max.id ) AS id FROM BI_STATISTICS.device_offline_log AS max GROUP BY max.deviceSn
) a
)

老数据修改

UPDATE BI_STATISTICS.device_offline_log SET onlineTime = (offlineTime + 1000)
WHERE
ISNULL( onlineTime )
AND id NOT IN (
SELECT a.id FROM (
SELECT max( max.id ) AS id FROM BI_STATISTICS.device_offline_log AS max GROUP BY max.deviceSn
) a
)

问题复盘

第一次写法

SELECT log.* ,ma.ma_id from  device_offline_log log
LEFT JOIN
 (
 -- 	查询所有设备最后一次的id 
	SELECT
		max.id  as ma_id
	FROM
		( SELECT DISTINCT a.deviceSn, a.id, 
		a.offlineTime, a.onlineTime 
		FROM device_offline_log a 
		ORDER BY a.offlineTime DESC ) as max 
		GROUP BY
		max.deviceSn 
	) ma

– 在线时间为null,并且不在最后一次的数据中
on ma.ma_id = log.id WHERE ISNULL(log.onlineTime) AND ISNULL(ma.ma_id);

之前一直使用order by 导致数据not 查不出数据，但是

	SELECT
		max.id  as ma_id
	FROM
		( SELECT DISTINCT a.deviceSn, a.id, 
		a.offlineTime, a.onlineTime 
		FROM device_offline_log a 
		ORDER BY a.offlineTime DESC ) as max 
		GROUP BY
		max.deviceSn `

	查出的结果集都满足要求的。但是联合到一起数据就不对。

后优化到：

SELECT
		max(max.id)  as ma_id
	FROM
		( SELECT DISTINCT a.deviceSn, a.id, 
		a.offlineTime, a.onlineTime 
		FROM device_offline_log a 
		ORDER BY a.offlineTime DESC ) as max 
		GROUP BY
		max.deviceSn `

就可以了。

总结，注意使用，max()