mysql中的Groupwise删除

Question

假设您有一张桌子，类似于：

create table user_news (
  user_id int unsigned not null, 
  article_id int unsigned not null, 
  article_date timestamp, 
  primary key (user_id, article_id), 
  key (user_id, article_date));

如何确保每个user_id不超过30行？也就是说，如何删除按article_date排序的所有行，偏移量为每user_id 30 。

Answer 1

注意：下面的查询使用>= 2，因为测试数据集很小。为您的解决方案将其更改为>= 30。

这是DELETE语句。

delete from x
using  user_news x inner join
    (
    select a.user_id, a.article_id
    from user_news a
    where (
      select count(*)
      from user_news b
      where a.user_id = b.user_id
        and ((a.article_date < b.article_date)
          or (a.article_date = b.article_date
          and a.article_id < b.article_id))
       ) >= 2
    ) C on c.user_id = x.user_id and c.article_id = x.article_id

基于这个测试表

create table user_news(
    user_id int,
    article_id int,
    article_date timestamp,
    primary key(user_id, article_id));
insert into user_news select 1,2,'2010-01-02';
insert into user_news select 1,3,'2010-01-03';
insert into user_news select 1,4,'2010-01-01'; # article_id order != date order
insert into user_news select 2,1,'2010-01-01';
insert into user_news select 2,2,'2010-01-02'; # 3 ties on date
insert into user_news select 2,3,'2010-01-02';
insert into user_news select 2,4,'2010-01-02';
insert into user_news select 4,5,'2010-01-05';

下面是内部SELECT查询，以查看发生了什么

select a.user_id, a.article_id
from user_news a
where (
  select count(*)
  from user_news b
  where a.user_id = b.user_id   # correlate the user_ids
        # earlier articles. for tie on date, break on article_id
    and ((a.article_date < b.article_date)
      or (a.article_date = b.article_date and a.article_id < b.article_id))
   ) >= 2