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

Question

需求是把一个用户登录记录的表（例如叫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;`

Answer 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
)