Mysql 多重分组排序
写存储过程的时候碰见了这样一个需求,按照每年的每个月给每个机构做一个排名,简单的贴个代码:
SELECT game_year,game_year_month,organization_id,sub_organization_id,game_cnt,cnt_rank,sub_organization_name
FROM
(
SELECT prc_import_app_ama_game_org_counts.*,IF(@p=game_year_month AND @z=organization_id,@r:=@r+1,@r:=1) AS cnt_rank,@p:=game_year_month,@z:=organization_id
FROM prc_import_app_ama_game_org_counts,(SELECT @p:=NULL,@z:=NULL,@r:=0)r
ORDER BY game_year_month,game_cnt DESC
)s;