Mysql group by top N的问题

http://sqlfiddle.com/#!2/4c0a5/32

>>>>> 建表

create table mytable
(
  person varchar(10),
  `group` int,
  age int
);
insert into mytable values
('Bob', 1, 32),
('Jill', 1, 34),
('Shawn', 1, 42),
('Jake', 2, 29),
('Paul', 2, 36),
('Laura', 2, 39);

>>>> 查询

set @num := 0, @group := '';

select person, `group`, age
from 
(
   select person, `group`, age,
      @num := if(@group = `group`, @num + 1, 1) as row_number,
      @group := `group` as dummy
  from mytable
  order by `Group`, Age desc
) as x where x.row_number <= 2;

===== http://www.2cto.com/database/201309/247468.html

   在日常工作中,经常要查询分组的前几名.oracle中可以通过row_num来支持查询, mysql暂时不支持row_num.那么如何来完成这个需求呢?

   例如：

表中的数据：

?

1 2 3 4 5 6 7 8 9 10

+ --------+-------+-----+ | Person | Group | Age | + --------+-------+-----+ | Bob    | 1     | 32  | | Jill   | 1     | 34  | | Shawn  | 1     | 42  | | Jake   | 2     | 29  | | Paul   | 2     | 36  | | Laura  | 2     | 39  | + --------+-------+-----

期望的结果：

1 2 3 4 5 6

+ --------+-------+-----+ | Shawn  | 1     | 42  | | Jill   | 1     | 34  | | Laura  | 2     | 39  | | Paul   | 2     | 36  | + --------+-------+-----

   方式一:借鉴oracle中row_num的思想,在sql中增加伪列.

?

1 2 3 4 5 6 7 8 9 10 11 12

set @num := 0, @ group := '' ;   select person, ` group `, age from (     select person, ` group `, age,        @num := if(@ group = ` group `, @num + 1, 1) as row_number,        @ group := ` group ` as dummy    from mytable    order by ` Group `, Age desc , person ) as x where x.row_number <= 2;

方式二:利用关联子查询

?

1 2 3 4

SELECT a.person, a. group , a.age FROM person AS a WHERE ( SELECT COUNT (*) FROM person AS b WHERE b. group = a. group AND b.age >= a.age) <= 2 ORDER BY a. group ASC , a.age DESC