MySQL-MYSQL 如何查询分组排序后再对每组LIMIT n

Question

遇到一个问题，有这样一个表：
mysql> desc recordMap;

+-------------+---------------------+------+-----+---------+----------------
| Field       | Type                | Null | Key | Default | Extra          
+-------------+---------------------+------+-----+---------+----------------
| id          | int(11) unsigned    | NO   | PRI | NULL    | auto_increment |
| mapId       | int(10) unsigned    | NO   |     |         |               |
| playTime    | int(10) unsigned    | NO   |     |         |
| ...后面还有一些其他数据                
+-------------+---------------------+------+-----+---------+----------------

Answer 1

http://wenku.it168.com/d_000791824.shtml
这里有好几种方法可以参考一下

Answer 2

写个function

 CREATE FUNCTION `new_func`(mapid INTEGER(11))
RETURNS tinyint(4)
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
begin

if (mapid = @map) then
set @auto_id = @auto_id +1;
else
set @auto_id = 1;
set @map = mapid;
end if;
return(@auto_id);
end;

select * from
(
select *, new_func(mapid) auto_id
from recordMap

order by mapid,playTime
) tmp
where tmp.auto_id <=2

Answer 3

刚才在本机试了下｀这个sql是允许并列的,处理的办法暂时用mysql没想出来｀
可以用程序去处理下｀同组下留两条

Answer 4

试试这个sql语句，有可能还要需要改进下。

 SELECT id,mapId,playTime,COUNT(playTime) AS num FROM recordMap GROUP BY playTime,mapID HAVING num=2 ORDER BY mapId,playTime

Answer 5

给你一个参考链接吧，实现起来挺复杂的，而且效率也不会很高，建议不要在mysql中实现
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/