SQL中无法按最高NewCount筛选最新徽章问题求助

错误原因分析 #

• 你错误地将tb_cards.NewCount作为分组字段，这会把每个不同的NewCount值单独分成一组，同时SELECT中的ROMCode和oldextid既不属于聚合函数结果，也不在GROUP BY列表中（标准SQL不允许这种写法，MySQL宽松模式下会返回随机的ROMCode），完全不符合你获取最大NewCount对应记录的需求。
• 若将ROMCode和oldextid加入GROUP BY，由于每个徽章的ROMCode唯一，每条记录会被视为独立分组，最终还是返回所有徽章记录，无法筛选出最新的那一枚。

正确SQL写法 #

方法一：子查询关联（兼容多数数据库）

SELECT tc.ROMCode AS 'MifareID', tu.oldextid AS 'persnr', tc.NewCount AS 'NewCount'
FROM tb_Cards tc
RIGHT JOIN tb_Users tu ON tc.id_user = tu.id_user
WHERE tu.oldextid = 'O1551'
AND tc.NewCount = (
    SELECT MAX(NewCount)
    FROM tb_Cards
    WHERE id_user = tu.id_user
);

方法二：窗口函数（适用于MySQL 8+、SQL Server等支持窗口函数的数据库）

SELECT MifareID, persnr, NewCount
FROM (
    SELECT tc.ROMCode AS 'MifareID', tu.oldextid AS 'persnr', tc.NewCount AS 'NewCount',
           ROW_NUMBER() OVER (PARTITION BY tu.id_user ORDER BY tc.NewCount DESC) AS rn
    FROM tb_Cards tc
    RIGHT JOIN tb_Users tu ON tc.id_user = tu.id_user
    WHERE tu.oldextid = 'O1551'
) AS t
WHERE rn = 1;

说明 #

• 方法一先通过子查询获取该用户对应的最大NewCount值，再筛选出匹配该值的记录，确保只返回最新徽章。
• 方法二利用窗口函数给每个用户的徽章按NewCount降序编号，取编号为1的记录即为最新徽章，这种方式扩展性更强，如需获取前N条最新记录只需修改rn <= N即可。

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