Mysql group by top N的问题

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

   例如：

表中的数据：

 

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

 

期望的结果：

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

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

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;

 

方式二:利用关联子查询

SELECT a.person, a.group, a.age FROM person AS a WHERE(SELECT COUNT(*)FROM person AS b 
WHERE b.group= a.groupAND b.age >= a.age)<=2ORDERBY a.groupASC, a.age DESC

 

ps：

1.在我处理的实际问题而言,方式一的效率明显高于方式二.

参考链接:http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results.