【字节跳动】使用 LATERAL JOIN 查询骑行路线个人最佳 Top2
JOIN 连接窗口函数排行榜面试真题
题目描述
来源：字节跳动 SQL 面试真题
某骑行运动社区记录了骑手在各条路线上的每次骑行数据。同一骑手可以在同一条路线上多次骑行。社区需要为每条路线生成“个人最佳排行榜”：先取出每位骑手在该路线上的个人最快成绩，再从这些个人最佳成绩中选出前 2 名，展示在路线主页。
数据表
cycling_routes 骑行路线表
| 字段 | 类型 | 说明 |
|---|---|---|
| route_id | INT | 路线编号，主键 |
| route_name | VARCHAR(50) | 路线名称 |
| distance_km | DECIMAL(6,2) | 路线总长度（公里） |
| difficulty | VARCHAR(10) | 难度等级：简单 / 中等 / 困难 |
| city | VARCHAR(20) | 所在城市 |
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 的骑手记录。
个人最佳规则
同一骑手在同一路线上可能有多次骑行记录，仅保留完成用时最短的那一次作为个人最佳。如果最短用时有多条记录，依次按以下规则取一条：
- 骑行日期
ride_date更早的记录优先。 - 如果日期也相同，
ride_id更小的记录优先。
路线 Top2 规则
在每条路线的所有个人最佳记录中，按以下规则取前 2 名：
completion_min升序。- 如果用时相同，
ride_date升序。 - 如果日期也相同，
ride_id升序。
骑手不足 2 人时，输出全部骑手；没有骑行记录的路线不输出。
输出字段
| 字段 | 说明 |
|---|---|
| route_name | 路线名称 |
| distance_km | 路线距离 |
| rider_name | 骑手名称 |
| ride_date | 个人最佳骑行日期 |
| completion_min | 个人最佳完成用时 |
| avg_speed_kmh | 个人最佳记录的平均速度 |
结果按 route_id 升序排列；同一路线内按上述个人最佳排名规则排列。
数据样例
| route_idPKINT | route_nameVARCHAR(50) | distance_kmDECIMAL(6, 2) | difficultyVARCHAR(10) | cityVARCHAR(20) |
|---|---|---|---|---|
| 1 | 城市环湖线 | 18.5 | 中等 | 上海 |
| 2 | 山海挑战线 | 32.8 | 困难 | 深圳 |
| 3 | 江畔休闲线 | 8.6 | 简单 | 杭州 |
| 4 | 晨雾无人线 | 12 | 简单 | 苏州 |
输入数据显示 4 / 4 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里