如何用SQL获取每个用户最高的resultPoint及对应resultPercentage

我是SQL新手，当前使用的查询语句如下：

SELECT DISTINCT
info_person.userUsername,
infoExamResult.resultPoint,
infoExamResult.resultPercentage,
infoExamResult.optAnswer
FROM info_person
INNER JOIN infoExamResult ON infoExamResult.personID = info_person.personID
where infoExamResult.datetimeStart BETWEEN "2024-03-18" AND "2024-03-26"
and infoExamResult.optAnswer = "COMMIT"
ORDER BY info_person.userUsername

当前查询输出结果：

+-------------------+-------------------+------------------+-----------+
|    userUsername   |    resultPoint   |  ResultPercentage | optAnswer |
+-------------------+-------------------+------------------+-----------+
|    NSO001000      |      20          |       50%         |   COMMIT  |
|    NSO001000      |      10          |       25%         |   COMMIT  |
|    NSO002000      |      40          |       100%        |   COMMIT  |
|    NSO002000      |      30          |       75%         |   COMMIT  |
+-------------------+-------------------+------------------+-----------+

我需要获取每个userUsername对应的最高resultPoint记录，期望输出如下：

+-------------------+-------------------+------------------+-----------+
|    userUsername   |    resultPoint   |  ResultPercentage | optAnswer |
+-------------------+-------------------+------------------+-----------+
|    NSO001000      |      20          |       50%         |   COMMIT  |
|    NSO002000      |      40          |       100%        |   COMMIT  |
+-------------------+-------------------+------------------+-----------+

解决方案 #

方法1：窗口函数（推荐，适配现代SQL数据库） #

窗口函数可以直接按用户分组并标记最高分记录，逻辑清晰且效率较高，支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库：

SELECT userUsername, resultPoint, resultPercentage, optAnswer
FROM (
    SELECT 
        info_person.userUsername,
        infoExamResult.resultPoint,
        infoExamResult.resultPercentage,
        infoExamResult.optAnswer,
        -- 按用户分组，每组内按分数降序排序，最高分记为第1条
        ROW_NUMBER() OVER (PARTITION BY info_person.userUsername ORDER BY infoExamResult.resultPoint DESC) AS rank_num
    FROM info_person
    INNER JOIN infoExamResult ON infoExamResult.personID = info_person.personID
    WHERE infoExamResult.datetimeStart BETWEEN "2024-03-18" AND "2024-03-26"
      AND infoExamResult.optAnswer = "COMMIT"
) AS ranked_results
-- 只保留每组的第1条记录（即最高分）
WHERE rank_num = 1
ORDER BY userUsername;

方法2：子查询（兼容旧版数据库） #

如果你的数据库不支持窗口函数，可以先通过子查询找出每个用户的最高分，再关联原表获取对应记录：

SELECT 
    p.userUsername,
    er.resultPoint,
    er.resultPercentage,
    er.optAnswer
FROM info_person p
INNER JOIN infoExamResult er 
    ON er.personID = p.personID
INNER JOIN (
    -- 先找出符合条件的每个用户的最高分
    SELECT personID, MAX(resultPoint) AS max_score
    FROM infoExamResult
    WHERE datetimeStart BETWEEN "2024-03-18" AND "2024-03-26"
      AND optAnswer = "COMMIT"
    GROUP BY personID
) AS user_max_scores 
    ON er.personID = user_max_scores.personID 
    AND er.resultPoint = user_max_scores.max_score
WHERE er.datetimeStart BETWEEN "2024-03-18" AND "2024-03-26"
  AND er.optAnswer = "COMMIT"
ORDER BY p.userUsername;

注意：如果同一用户存在多条分数相同的最高分记录，此方法会返回所有这些记录；若只需单条，可添加DISTINCT关键字。

内容的提问来源于stack exchange，提问作者Wu C