【背景】 某骑行运动社区记录了骑手在各条路线上的每次骑行数据。同一骑手可以在同一条路线上多次骑行。社区需要为每条路线生成"个人最佳排行榜"——先取出每位骑手在该路线上的个人最快成绩，再从这些最快成绩中选出前2名展示在路线主页。 【表结构与字段说明】 表1：cycling_routes（骑行路线表） route_id：INT，路线编号，主键 route_name：VARCHAR(50)，路线名称 distance_km：DECIMAL(6,2)，路线总长度（公里） difficulty：VARCHAR(10)，难度等级（简单中等困难） city：VARCHAR(20)，所在城市 表2：ride_records（骑行记录表） ride_id：INT，骑行记录编号，主键 route_id：INT，路线编号，关联 cycling_routes.route_id rider_name：VARCHAR(30)，骑手名称 ride_date：DATE，骑行日期 completion_min：INT，完成用时（分钟，保证 = 1） avg_speed_kmh：DECIMAL(5,2)，平均速度（公里小时） 三、问题 请使用 LATERAL JOIN 查询每条骑行路线上个人最佳成绩排名前2的骑手记录。具体规则如下：（1）同一骑手在同一路线上可能有多次骑行记录，仅保留其完成用时(completion_min)最短的那一次作为个人最佳；若同一骑手有多次用时相同的最短记录，取骑行日期(ride_date)最早的一次，若日期也相同则取 ride_id 最小的一次；（2）在所有骑手的个人最佳中，按 completion_min 升序排列取前2名，若 completion_min 相同则按 ride_date 升序排列，若 ride_date 也相同则按 ride_id 升序排列。 输出以下字段：路线名称(route_name)、距离(distance_km)、骑手名称(rider_name)、骑行日期(ride_date)、完成用时(completion_min)、平均速度(avg_speed_kmh)。结果按 route_id 升序排列，同一路线内按上述个人最佳排名规则排列。若某路线骑手不足2人，则显示全部骑手；若无骑行记录则该路线不出现在结果中。 四、示例数据表 cycling_routes 表： route_id route_name distance_km difficulty city 1 滨江骑行道 25.00 中等 杭州 2 环太湖赛道 68.50 困难 苏州 ride_records 表： ride_id route_id rider_name ride_date completion_min avg_speed_kmh 1 1 Alice 2025-03-01 55 27.27 2 1 Alice 2025-03-10 50 30.00 3 1 Bob 2025-03-05 48 31.25 4 1 Bob 2025-03-15 52 28.85 5 1 Carol 2025-03-08 50 30.00 6 1 Carol 2025-03-18 50 30.00 7 2 Dave 2025-03-02 180 22.83 8 2 Eve 2025-03-12 165 24.91 五、示例数据查询结果表 说明： 滨江骑行道：Alice个人最佳=50min(ride_id=2)，Bob个人最佳=48min(ride_id=3)，Carol个人最佳=50min(ride_id=5，两次50min取较早的03-08)。三人最佳排序：Bob(48) → Carol(50,03-08) → Alice(50,03-10)。取前2：Bob、Carol。 环太湖赛道：Dave个人最佳=180min，Eve个人最佳=165min。排序：Eve(165) → Dave(180)。取前2：Eve、Dave。 route_name distance_km rider_name ride_date completion_min avg_speed_kmh 滨江骑行道 25.00 Bob 2025-03-05 48 31.25 滨江骑行道 25.00 Carol 2025-03-08 50 30.00 环太湖赛道 68.50 Eve 2025-03-12 165 24.91 环太湖赛道 68.50 Dave 2025-03-02 180 22.83
示例1

输入

CREATE TABLE cycling_routes (
    route_id INT PRIMARY KEY,
    route_name VARCHAR(50),
    distance_km DECIMAL(6,2),
    difficulty VARCHAR(10),
    city VARCHAR(20)
);

CREATE TABLE ride_records (
    ride_id INT PRIMARY KEY,
    route_id INT,
    rider_name VARCHAR(30),
    ride_date DATE,
    completion_min INT,
    avg_speed_kmh DECIMAL(5,2)
);

INSERT INTO cycling_routes VALUES
(1, '滨江骑行道', 25.00, '中等', '杭州'),
(2, '环太湖赛道', 68.50, '困难', '苏州');

INSERT INTO ride_records VALUES
(1, 1, 'Alice', '2025-03-01', 55, 27.27),
(2, 1, 'Alice', '2025-03-10', 50, 30.00),
(3, 1, 'Bob', '2025-03-05', 48, 31.25),
(4, 1, 'Bob', '2025-03-15', 52, 28.85),
(5, 1, 'Carol', '2025-03-08', 50, 30.00),
(6, 1, 'Carol', '2025-03-18', 50, 30.00),
(7, 2, 'Dave', '2025-03-02', 180, 22.83),
(8, 2, 'Eve', '2025-03-12', 165, 24.91);

输出

route_name|distance_km|rider_name|ride_date|completion_min|avg_speed_kmh
滨江骑行道|25.00|Bob|2025-03-05|48|31.25
滨江骑行道|25.00|Carol|2025-03-08|50|30.00
环太湖赛道|68.50|Eve|2025-03-12|165|24.91
环太湖赛道|68.50|Dave|2025-03-02|180|22.83
加载中...