mysql分组后排序

创建表：

CREATE TABLE `test_user_scores` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_id` int NOT NULL DEFAULT '0' COMMENT '用户ID',
  `group_id` int NOT NULL DEFAULT '0' COMMENT '分组ID',
  `scores` int NOT NULL DEFAULT '0' COMMENT '分数',
  PRIMARY KEY (`id`),
  KEY `ind_user_id` (`user_id`),
  KEY `ind_group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='分组分数表';

插入数据

INSERT INTO `test`.`test_user_scores` (`id`, `user_id`, `group_id`, `scores`) 
VALUES (1, 1, 1, 80),
(2, 1, 2, 78),
(3, 1, 1, 99),
(4, 2, 2, 11),
(5, 2, 2, 88),
(6, 2, 2, 77),
(7, 1, 1, 18),
(8, 1, 1, 78),

mysql 版本：

SELECT version();  -- 8.0.25

1. 取每人每组最高分

SELECT user_id,group_id, max(scores) as max_scores
from test_user_scores
GROUP BY user_id,group_id
;

2. 取每人top3分数

SELECT user_id,scores,row_num 
from (
SELECT user_id,scores,IF(@field=user_id,@rownum:=@rownum+1,@rownum:=1) as row_num, @field:=user_id
FROM
    ( SELECT user_id, scores 
    from test_user_scores
    order by user_id, scores desc 
    ) a , ( SELECT @rownum:=0,@field:='') b 
) c WHERE c.row_num <=3
;

3. 取每人每组top3分数

SELECT user_id,group_id,scores,row_num1,row_num2 
from (
    SELECT user_id,group_id,scores,IF(@field=user_id,@rownum1:=@rownum1+1,@rownum1:=1) as row_num1,
    IF(@field=user_id && @field2=group_id,@rownum2:=@rownum2+1,@rownum2:=1) as row_num2,    @field:=user_id,@field2:=group_id 
    FROM
        ( 
            SELECT user_id,group_id, scores 
            from test_user_scores
            order by user_id,group_id, scores desc 
        ) a , ( SELECT @rownum1:=0, @rownum2:=0,@field:='',@field2:='') b 
) c WHERE c.row_num2 <=3
;

4. 使用窗口函数实现

SELECT row_number()over(partition by group_id,user_id order by scores desc) as rn,user_Id,group_id,scores 
FROM test_user_scores

参考资料：