MySql分组：组内取前*条数据

MySql分组：组内取前*条数据，例如：查询每关最先通关的15个玩家信息。

-- 获取分组，每组前*条，id字符串集合
SELECT
    GROUP_CONCAT( topPassMissionInfo SEPARATOR '--------------' ) 
FROM
    ( SELECT missionId, SUBSTRING_INDEX( GROUP_CONCAT( userId ORDER BY passTime DESC ), ',', 15 ) AS topPassMissionInfo FROM t_u_player_pass_mission GROUP BY missionId ) a

结果如下：

 

 

 

 

 再根据userId字段的in条件去查询详细信息即可：SQL如下，因为group_concat函数获得的字符串，只会匹配第一个，之后 的就都不匹配了此时需要使用 FIND_IN_SET 函数，

SELECT
    * 
FROM
    t_u_table 
WHERE
    FIND_IN_SET(
        userId,
        (
        SELECT
            GROUP_CONCAT( topPassMissionInfo SEPARATOR ',' ) 
        FROM
            ( SELECT missionId, SUBSTRING_INDEX( GROUP_CONCAT( userId ORDER BY passTime DESC ), ',', 15 ) AS topPassMissionInfo FROM t_u_player_pass_mission GROUP BY missionId ) a 
        ) 
    );

 

 

如果主键是联合主键，不是单个字段（上例中为userId）,可结合CONCAT函数使用

如下：联合主键为 missionId 和 userId

-- 最早通关
SELECT
    *FROM
    t_u_player_pass_mission 
WHERE
    FIND_IN_SET(
        CONCAT( missionId, "_", userId ),
        (
        SELECT
            GROUP_CONCAT( topPassMissionInfo SEPARATOR ',' ) 
        FROM
            (
            SELECT
                missionId,
                SUBSTRING_INDEX( GROUP_CONCAT( CONCAT( missionId, "_", userId ) ORDER BY passTime ), ',', 15 ) AS topPassMissionInfo 
            FROM
                t_u_player_pass_mission 
            GROUP BY
                missionId 
            ) a 
        ) 
    );