记录：sql group rank

表结构：

需求：按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)