统计一个表中 每个用户分数最大的 记录

SELECT  e1.id AS id ,e1.user_id, e1.score AS max_score 

FROM test_score e1  

LEFT JOIN test_score e2  

ON e1.user_id = e2.user_id AND e1.score < e2.score  

WHERE e2.score IS NULL;

相关表结构是：

CREATE TABLE `test_score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `score` int DEFAULT NULL COMMENT '分数',
  `user_id` int DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test_score` VALUES (1, 10, 1);
INSERT INTO `test_score` VALUES (2, 20, 1);
INSERT INTO `test_score` VALUES (3, 5, 1);
INSERT INTO `test_score` VALUES (4, 30, 3);
INSERT INTO `test_score` VALUES (5, 20, 3);
INSERT INTO `test_score` VALUES (6, 100, 5);
INSERT INTO `test_score` VALUES (7, 200, 5);
INSERT INTO `test_score` VALUES (8, 150, 5);

查询的结果是：

 案例二：

表结构：

CREATE TABLE `ftb_cultivate_exam_user` (
  `F_Id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `F_ExamId` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '考试主键ID',
  `F_ExamType` tinyint DEFAULT NULL COMMENT '考试类型,0岗位学习考试,1常规考试',
  `F_UserId` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户主键ID',
  `F_Duration` bigint DEFAULT NULL COMMENT '用时，单位秒',
  `F_Score` int DEFAULT NULL COMMENT '总考分',
  `F_UserStartTime` datetime DEFAULT NULL COMMENT '用户开始考试时间',
  `F_FinishTime` datetime DEFAULT NULL COMMENT '完成考试时间',
  `F_Status` tinyint NOT NULL COMMENT '考试状态（0待考试，1待批阅，2已逾期，3合格，4不合格 5、优秀）',
  PRIMARY KEY (`F_Id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='考试用户表';



SELECT
    e1.F_UserId,
    e1.F_Score AS max_score,
    e1.*
FROM
    ftb_cultivate_exam_user e1
    JOIN ( SELECT F_UserId, MAX( F_Score ) AS max_score FROM ftb_cultivate_exam_user GROUP BY F_UserId ) e2 ON e1.F_UserId = e2.F_UserId
    AND e1.F_Score = e2.max_score order by max_score desc;