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
结果如下:
因为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
)
);
源码,是痛苦的,又是快乐的,如果没有这痛苦,也就没有了这快乐!