如何在MySQL中实现无重复运动员的排序查询并支持分页？

给定以下表结构及测试数据：

CREATE TABLE `a_athletes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `a_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `athlete_id` bigint(20) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `a_events` (`id`, `name`, `athlete_id`, `date`) VALUES
(1, 'Long jump', 1, '2023-02-18'),
(2, 'Row', 1, '2023-02-09'),
(3, 'Sprint', 2, '2023-02-10'),
(4, 'Sprint', 1, '2023-02-14'),
(5, 'Long Jump', 2, '2023-02-20');

INSERT INTO `a_athletes` (`id`, `name`) VALUES
(1, 'Sarah'),
(2, 'Simon'),
(3, 'Barbera');

需要筛选出所有未参与Row赛事的运动员，现有查询能完成基础筛选，但会返回同一运动员的多条赛事记录：

select * 
from a_athletes
left join a_events on a_athletes.id = a_events.athlete_id
where a_athletes.id not in (
    select a_athletes.id
    from a_athletes
    inner join a_events on a_athletes.id = a_events.athlete_id
    where a_events.`name` = 'Row'
)
 order by a_events.`date`;

返回结果（存在重复运动员条目）：

3,Barbera,NULL,NULL,NULL,NULL
2,Simon,3,Sprint,2,2023-02-10
2,Simon,5,Long Jump,2,2023-02-20

要求：每个运动员仅显示一条记录（忽略后续重复的运动员行），且必须在SQL层面实现该逻辑以支持分页，预期输出如下：

3,Barbera,NULL,NULL,NULL,NULL
2,Simon,3,Sprint,2,2023-02-10

使用窗口函数ROW_NUMBER()为每个运动员的记录分配排序序号，筛选出每组的第一条记录即可实现需求，优化后的SQL如下：

SELECT *
FROM (
    SELECT 
        a_athletes.id,
        a_athletes.name,
        a_events.id AS event_id,
        a_events.name AS event_name,
        a_events.athlete_id AS event_athlete_id,
        a_events.date AS event_date,
        -- 按运动员分组，按赛事日期升序排序，分配序号
        ROW_NUMBER() OVER (PARTITION BY a_athletes.id ORDER BY a_events.date ASC) AS row_num
    FROM a_athletes
    LEFT JOIN a_events ON a_athletes.id = a_events.athlete_id
    -- 简化筛选逻辑：直接排除参与过Row赛事的运动员ID
    WHERE a_athletes.id NOT IN (
        SELECT athlete_id FROM a_events WHERE name = 'Row'
    )
) AS filtered_data
-- 仅保留每个运动员的第一条记录
WHERE row_num = 1
ORDER BY event_date;

关键逻辑说明： #

• PARTITION BY a_athletes.id：将结果按运动员ID分组，确保每个运动员的记录单独排序。
• ORDER BY a_events.date ASC：对每组内的赛事记录按日期升序排列，保证最早的赛事记录排在前面（无赛事的运动员仅一条NULL记录，序号为1）。
• WHERE row_num = 1：筛选出每组的第一条记录，实现每个运动员仅显示一条的需求。
• 简化筛选子查询：原查询中关联a_athletes的子查询可以简化为直接从a_events中获取参与Row赛事的运动员ID，逻辑更高效。

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