删除行:没有单个成员的记录多于x条 [英] Deleting Rows: No Single Member Has More Than x Records

问题描述

我有一个这样的表:

CREAT TABLE `member_logins` (
    `id` bigint(10) unsigned not null auto_increment,
    `member_id` mediumint(8) unsigned not null,
    `date_created` datetime not null,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB;

我只想为每个成员保留50个登录记录.因此，我正在寻找一种以每个成员为基础删除行的方法，该行可以删除最近50行之后的任何行.

I only want to keep 50 logins recorded for each member. So I'm looking for a way to DELETE rows on a per member basis, any rows past the most recent 50.

我应该提到过……这将是每晚的cron工作.不需要实时进行任何操作.

I should have mentioned... This would be a nightly cron job. Not something that needs to be done in real time.

推荐答案

  DELETE FROM member_logins
  WHERE id in(
       SELECT ID
       FROM (SELECT 
               ID,member_id, 
               IF( @prev <> member_id, @rownum := 1, @rownum := @rownum+1 ) AS  rank, 
               @prev := member_id,date_created 
               FROM member_logins t 
               JOIN (SELECT @rownum := NULL, @prev := 0) AS r 
            ORDER BY t.member_id,t.date_created desc) as tmp
        where tmp.rank > 2)

解决了该查询.经过样本数据测试，对我有用.

Fixed the query. Tested on sample data and it works for me.

我加载进行测试的样本数据

Sample Data i loaded for testing

id  member_id   date_created
1   1   2/26/2011 12:00:00 AM
2   1   5/26/2011 12:00:00 AM
3   1   4/26/2011 12:00:00 AM
4   2   5/26/2011 12:00:00 AM
5   2   3/26/2011 12:00:00 AM
6   2   4/26/2011 12:00:00 AM

我在查询中排在前2行，而不是50行.

Instead of 50, i have in the query top 2 rows for testing.

因此，我的查询应删除每个member_id组中排名> 2的所有行，这些行中按date_created desc排序的行.

So my query should delete all rows which are having rank > 2 in each member_id group where in rows ordered by date_created desc.

运行删除查询后的输出:

Output after running the delete query:

id  member_id   date_created
2   1   5/26/2011 12:00:00 AM
3   1   4/26/2011 12:00:00 AM
4   2   5/26/2011 12:00:00 AM
6   2   4/26/2011 12:00:00 AM

您可以看到ID为1和5的行已被删除.这些是每个member_id组中的排名> 2的行

You can see the rows with ID 1 and 5 got deleted. These are the rows having rank > 2 in each member_id group

这篇关于删除行:没有单个成员的记录多于x条的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！