删除数据表中重复记录（anlan）

1.一小时只保留一条有效记录，查询重复记录

select id, sensor_uuid, sensor_type, sensor_value,sensor_time, count(*) from xz_sensor_data group by sensor_uuid,sensor_time having count(*) > 1;

2.删除重复记录中，id较小的一条

delete from xz_sensor_data where id in (select min(id) from xz_sensor_data group by sensor_uuid,sensor_time having count(*) > 1);

执行该语句报错：1093 - You can't specify target table 'xz_sensor_data' for update in FROM clause

上网查了一下，针对这种问题可以通过”绕”的方式进行实现。

错误实现方式，删除了所有数据：

delete from xz_sensor_data where id in (select id from (select min(id) from xz_sensor_data group by sensor_uuid,sensor_time having count(*) > 1) as temp)

正确实现方式，删除了查询出的重复记录中，id较小的数据。

delete from xz_sensor_data where id in (select id from (select id from xz_sensor_data group by sensor_uuid,sensor_time having count(*) > 1) as temp);

查询某一时间的数据：

select * from xz_sensor_data where sensor_time>='2021-03-01 00:56:00.012'