mysql如何把每个用户的记录保留最新10条

需求是把一个用户登录记录的表(例如叫signin_record)中每个用户的记录保留最新10条,
以下sql语句只能删除一个用户,请问如何改进?

`DELETE tb FROM signin_record AS tb ,(SELECT id FROM signin_record ORDER BY id desc LIMIT 1,9) AS tmp,(SELECT DISTINCT user FROM signin_record ORDER BY user desc) AS users
WHERE tb.id<tmp.id and tb.user=users.user;`

阅读 5.1k
1 个回答

先查出每个用户时间10条以后记录

select t1.id 
from signin_record t1 
  left join signin_record t2 on t1.`user`=t2.`user` and t1.id<=t2.id
group by t1.`user`,t1.id
having count(1)>10

删除(建议备份下数据)

delete from signin_record where id in 
(select id from(
   select t1.id 
   from signin_record t1 
     left join signin_record t2 on t1.`user`=t2.`user` and t1.id<=t2.id
   group by t1.`user`,t1.id
   having count(1)>10
  )t
)
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题
宣传栏