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;`

阅读 870
评论
    1 个回答
    • 1.4k

    先查出每个用户时间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
    )
      撰写回答

      登录后参与交流、获取后续更新提醒