sql - 限制sql查询中一列的连续值

Question

我有一个具有以下结构的表:

id           -int(11)
event_id     -int(11)
photo_id     -int(11)
created_at   -datetime

我如何编写一个查询来返回最近的 100 行，但要确保 photo_id 中具有相同值的连续行不超过 4 行

Answer 1

您可以添加一个 where 子句来过滤掉存在 4 行具有较低 photo_id 的行:

select *
from YourTable t1
where 4 > (
    select count(*)
    from YourTable t2
    where t1.event_id = t2.event_id
    and t1.photo_id < t2.photo_id
)
limit 100

对于巨大的表，这可能会有点慢。一个更快但非常 MySQL 特定的选项是使用变量。例如:

select *
from (
    select
        @nr := case 
            when event_id = @event then @nr + 1 
            else 1 
        end as photonr
    ,   @event := event_id
    ,   t1.*
    from YourTable as t1
    cross join (select @event := -1, @nr := 1) as initvars
    order by event_id
) as subquery
where subquery.photonr < 5
limit 100;

使用的测试数据:

drop table if exists YourTable;

create table YourTable (
  id int auto_increment primary key
, event_id int
, photo_id int
);

insert into YourTable (event_id, photo_id)
values (1,1), (1,2), (1,3), (1,4), (1,5), (2,1), (1,6);