MySQL查询筛选仅含较大Number值的部分重复记录

在MySQL数据库的某张表中，存在多组仅Number列值不同的记录对，示例数据如下：

| FirstName|LastName |Number|
| -------- | ------- |------|
| John     | Smith   |15    |
| John     | Smith   |32    |
....
| Sarah    | Baker   |8     |
| Sarah    | Baker   |14    |
....

需要编写SQL查询语句，实现两个要求：

1. 仅筛选出每组重复记录中Number值较大的那条
2. 排除仅存在单条记录的用户（即每人只有一条记录的情况）

预期查询结果如下：

| John     | Smith   |32    |
| Sarah    | Baker   |14    |

假设表名为your_table，以下提供两种可行的SQL写法：

方法一：兼容所有MySQL版本（使用分组+子查询） #

SELECT t.FirstName, t.LastName, t.Number
FROM your_table t
INNER JOIN (
    SELECT FirstName, LastName, MAX(Number) AS max_num
    FROM your_table
    GROUP BY FirstName, LastName
    HAVING COUNT(*) > 1  -- 只保留有重复记录的用户组
) AS grouped 
ON t.FirstName = grouped.FirstName 
AND t.LastName = grouped.LastName 
AND t.Number = grouped.max_num;

逻辑说明：子查询先按姓名分组，筛选出每组的最大Number，同时通过HAVING COUNT(*) > 1过滤掉只有单条记录的用户；再通过内连接匹配原表中对应的数据行，得到最终结果。

方法二：适用于MySQL 8.0+（使用窗口函数） #

如果你的MySQL版本是8.0及以上，用窗口函数的写法更简洁直观：

WITH ranked_records AS (
    SELECT 
        FirstName, 
        LastName, 
        Number,
        -- 按姓名分组，Number降序排名，每组第一条就是最大Number的记录
        ROW_NUMBER() OVER (PARTITION BY FirstName, LastName ORDER BY Number DESC) AS rn,
        -- 统计每组的记录总数
        COUNT(*) OVER (PARTITION BY FirstName, LastName) AS record_count
    FROM your_table
)
SELECT FirstName, LastName, Number
FROM ranked_records
WHERE rn = 1 AND record_count > 1;

逻辑说明：先用ROW_NUMBER()给每组记录按Number降序排号，用COUNT()统计每组的记录数；最后筛选出排名第一（即Number最大）且组内记录数大于1的行。

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