mysql - Group By 但指定每个分组 ID 的行数

Question

我有以下内容:

id | name  | Book Read        | Date Read
1    Maria   Dogs love cats     12/31/2008
1    Maria   Cats and Dogs      12/31/2007
1    Maria   Cowboys            12/31/2006
2    Tom     Cowboys            12/31/2008
2    Tom     Indians            12/31/2005
2    Tom     Cats and Dogs      12/31/2003
3    Harry   Raining hard       12/31/2005
3    Harry   Cats and Dogs      12/31/2002
3    Harry   Indians            12/31/2001

如果我执行“SELECT * FROM table GROUP BY id”，我得到:

id | name  | Book Read         | Date Read
1    Maria   Dogs love cats      12/31/2008
2    Tom     Cowboys             12/31/2008
3    Harry   Raining hard        12/31/2005

但是我如何为每个人获得前 2 本书？即:

id | name  | Book Read         | Date Read
1    Maria   Dogs love cats      12/31/2008
1    Maria   Cats and Dogs       12/31/2007
2    Tom     Cowboys             12/31/2008
2    Tom     Indians             12/31/2005
3    Harry   Raining hard        12/31/2005
3    Harry   Cats and Dogs       12/31/2002

Answer 1

假设你有一个自增字段，在我的例子中叫做 i

select * from table as t1
where (select count(*) from table as t2
       where t1.id = t2.id and t2.i < t1.i) <2

这是另一种选择

select tmp.i,tmp.id,tmp.name,tmp.book_read
from (
  select 
    id,i,name,book_read,
    if( @prev <> id, @r:=1,@r:=@r+1 ) as rank,
    @prev := id
  from table as t
  join (select @r:=null,@prev:=0) as rn
  order by t.id,t.i
) as tmp
where tmp.rank <= 2
order by i,id;