表结构:
需求:按guild_id分组,user_level前三的character_id,如果user_level相同,取character_id较小的行
如果是oracle、hive可以用 row_number over (PARTITION BY guild_id order by user_level desc, character_id asc)
mysql 可以有两种方法:
1:guild_id, GROUP_CONCAT(character_id order by user_level desc, character_id asc) as character_ids group by guild_id
上层再用SUBSTRING_INDEX(character_ids, ',', 1) as character_id, 1 as rank
2:临时变量
select * from
(select @rownum:=@rownum+1 as rownum,
a.character_id,
a.guild_id,
a.user_level,mid, role_name,
if(@gid=a.guild_id, @rank:=@rank+1, @rank:=1) as rank,
@gid:=a.guild_id as gid
from
(select character_id, guild_id, user_level, mid, role_name from user
where guild_id <> 0
order by guild_id asc, user_level desc, character_id asc ) a,
(select @rownum:=0, @gid:=0, @rank:=0)t)x
where rank in(1, 2, 3)