SQL分组查询：获取无重复最大值数据及用户最新记录筛选

看起来你需要分两步实现需求：先提取每个用户的最新date_time记录，再从这些记录里找出right_answers最大、若相同则timesec最小的那条。咱们一步步拆解问题，修正你的SQL问题：

一、先分析你之前的SQL问题 #

你的两个尝试都没命中核心需求：

• 第一个SQL用GROUP BY scores.user_id但没有聚合函数获取最新时间，且ORDER BY scores.date_time ASC取的是最早记录，加上非标准的GROUP BY用法（SELECT多列但仅按user_id分组），导致结果不可靠甚至重复。
• 第二个SQL没有过滤每个用户的最新记录，直接排序所有数据，所以会出现重复用户的多条记录，不符合“每个用户只留最新一条”的要求。

二、分步实现方案 #

步骤1：获取每个用户的最新记录 #

这里分两种情况，根据你的数据库版本选择：

方案A：支持窗口函数（MySQL 8+、PostgreSQL、SQL Server等）

用ROW_NUMBER()窗口函数给每个用户的记录按时间倒序编号，取编号为1的（最新记录）：

WITH user_latest AS (
    SELECT 
        u.firstname, u.fathername, u.lastname,
        s.user_id, s.right_answers, s.timesec, s.date_time,
        -- 按用户分组，时间倒序排，最新的记录编号为1
        ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY s.date_time DESC) AS rn
    FROM scores s
    INNER JOIN users u ON s.user_id = u.id
    WHERE s.group_id = '$groupID'
)
-- 只保留每个用户的最新记录
SELECT firstname, fathername, lastname, user_id, right_answers, timesec, date_time
FROM user_latest
WHERE rn = 1;

方案B：不支持窗口函数（MySQL 5.x等）

先通过子查询找出每个用户的最新时间，再关联原表获取对应记录：

SELECT 
    u.firstname, u.fathername, u.lastname,
    s.user_id, s.right_answers, s.timesec, s.date_time
FROM scores s
INNER JOIN users u ON s.user_id = u.id
-- 子查询得到每个用户的最新date_time
INNER JOIN (
    SELECT user_id, MAX(date_time) AS latest_dt
    FROM scores
    WHERE group_id = '$groupID'
    GROUP BY user_id
) latest ON s.user_id = latest.user_id AND s.date_time = latest.latest_dt
WHERE s.group_id = '$groupID';

步骤2：从最新记录中筛选最优结果 #

在步骤1的基础上，我们需要找出right_answers最大的记录；若有多个，取timesec最小的。

完整SQL（窗口函数版，更简洁）

WITH user_latest AS (
    SELECT 
        u.firstname, u.fathername, u.lastname,
        s.user_id, s.right_answers, s.timesec, s.date_time,
        ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY s.date_time DESC) AS rn
    FROM scores s
    INNER JOIN users u ON s.user_id = u.id
    WHERE s.group_id = '$groupID'
), final_ranked AS (
    SELECT 
        firstname, fathername, lastname, user_id, right_answers, timesec, date_time,
        -- 按right_answers降序、timesec升序排序，最优记录编号为1
        ROW_NUMBER() OVER (ORDER BY right_answers DESC, timesec ASC) AS final_rn
    FROM user_latest
    WHERE rn = 1
)
SELECT firstname, fathername, lastname, user_id, right_answers, timesec, date_time
FROM final_ranked
WHERE final_rn = 1;

完整SQL（非窗口函数版）

SELECT 
    u.firstname, u.fathername, u.lastname,
    s.user_id, s.right_answers, s.timesec, s.date_time
FROM scores s
INNER JOIN users u ON s.user_id = u.id
INNER JOIN (
    SELECT user_id, MAX(date_time) AS latest_dt
    FROM scores
    WHERE group_id = '$groupID'
    GROUP BY user_id
) latest ON s.user_id = latest.user_id AND s.date_time = latest.latest_dt
WHERE s.group_id = '$groupID'
-- 按规则排序后取第一条
ORDER BY s.right_answers DESC, s.timesec ASC
LIMIT 1;

三、验证逻辑 #

用你提供的测试数据：

1. 先提取每个用户的最新记录：
   • Fadi的最新记录是2020-11-24 11:24:00（right_answers=5，timesec=40）
   • Jake的最新记录是2020-11-24 11:54:00（right_answers=5，timesec=35）
   • Bob的最新记录是2020-11-24 11:59:00（right_answers=8，timesec=45）
2. 然后筛选right_answers最大的：Bob的8是最大的，所以最终结果应该是Bob的那条。（注：你提供的期望结果可能存在笔误，按数据逻辑最优记录是Bob的）

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