题解 | 骑行运动社区路线个人最佳排名

SELECT 
    cr.route_name,
    cr.distance_km,
    t.rider_name,
    t.ride_date,
    t.completion_min,
    t.avg_speed_kmh
FROM cycling_routes cr
CROSS JOIN LATERAL (
    -- 对于当前路线 cr.route_id，计算个人最佳排行榜前2名
    SELECT 
        rider_name,
        ride_date,
        completion_min,
        avg_speed_kmh
    FROM (
        -- 为每个骑手找到个人最佳记录（用时最短，同用时取最早日期、最小ride_id）
        SELECT 
            rider_name,
            ride_date,
            completion_min,
            avg_speed_kmh,
            ride_id,
            ROW_NUMBER() OVER (
                PARTITION BY rider_name 
                ORDER BY completion_min ASC, ride_date ASC, ride_id ASC
            ) AS rn
        FROM ride_records
        WHERE route_id = cr.route_id   -- 只考虑当前路线的记录
    ) best
    WHERE rn = 1   -- 每个骑手的最佳
    ORDER BY completion_min ASC, ride_date ASC, ride_id ASC  -- 在所有最佳中排序
    LIMIT 2        -- 取前2名
) AS t
ORDER BY cr.route_id ASC, t.completion_min ASC, t.ride_date ASC;