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

RUSSIAVK
  • 26

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

回复
阅读 1.4k
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
)
你知道吗?

宣传栏