MySQL中分组取第⼀条，以及删除多余的重复记录检查重复记录
-- 检查重复code1
select count(identity) num, identity from event_log
where code='code1'
group by identity having count(identity) >1
order by num desc
删除重复记录
DELETE FROM event_log WHERE `code`='code1'AND identity IN (
SELECT identity from (
mysql删除重复的数据保留一条SELECT identity FROM event_log WHERE code='code1'GROUP BY identity HAVING count(identity) >1
) a
) AND id NOT IN (
SELECT keepId FROM (
SELECT min(id) keepId FROM event_log WHERE code='code1'GROUP BY identity HAVING count(identity) >1
) b
)
其中 a 和 b 两个中间表的作⽤是, 避免执⾏时出现  You can't specify target table 'xxxxx' for update in FROM clause 错误
分组按时间正序取第⼀条记录, 巧妙地使⽤了not exists
select d.*from t_charge d where not exists (select1from t_charge where user_id= d.user_id and created_at < d.created_at)
按时间倒序则是
select f.*from t_charge f where not exists (select1from t_charge where user_id= f.user_id and created_at > f.created_at)