一条SQL语句来模拟火车票分配-2024数据库编程大赛-NineData-玖章算术

第二届数据库编程大赛

1. 只能使用一条 SQL ，可以使用数据库内置函数，不能使用自定义函数和存储过程

2. 一位乘客最多只能分配 1 个车次的车厢座位

3. 不能出现火车有空余座位，对于起始站和终点站乘客未分配票

4. 普通挑战火车不卖无座票，进阶挑战火车可以售卖 10% 的无座票

5. 不能出现在 train 以外的火车信息

6. 不能出现在 passenger 以外的乘客信息

7. 每个选手最多提交 3 次比赛代码

8. 提交的 SQL 不能超过 10 KB大小

备注 ( 选手需要验证 SQL 的逻辑正确性 ) ：

测试数据与验证步骤

1. 进入 NineData 官网（http://www.ninedata.cloud），注册并登录 NineData 控制台

2. 打开 SQL 窗口，申请一个 mysql demo 数据库

3. 查询 demo 表数据， game_ticket.train， game_ticket.passenger

4. 在 SQL 窗口编写参赛 SQL 并验证逻辑

5. 在 NineData 官网提交参赛 SQL

严禁互相抄袭答案，如果发现有雷同代码，组委会将取消成绩。

冠军挑战赛规则

1. 提交进阶挑战代码，数据量与比赛相同，100 万
2. 性能超越本次冠军，性能进入 1 秒以内
3. 获奖人数：1 人，性能最好者获得冠军挑战奖杯
4. 冠军不参加比赛

5. 新增数据库类型：DuckDB (v1.1.3)

6. 挑战时间：2024.12.30:22:00:00 ~ 2025.01.05:22:00:00

春运的火车票总是一票难求，现在假设你掌握了火车的车次信息，并且收集到了所有乘客的起始站和终点站需求，需要你用一条 SQL 为每个乘客分配火车票车次和座位，让乘客都尽可能坐上火车回家。

你收集的信息放在两张表里 ( train，passenger )

train 表中存放了车次信息，包括车次编号（主键），出发站，到达站，座位数（座位数是 600 , 800 , 1200 , 1600 四种之一）
比赛 demo 数据有 100 条记录，最终决赛 1000~10000 条记录

相同出发站和到达站可能会有多个车次

passenger 表保存了所有的乘客信息：乘客编号（主键），出发站，到达站

比赛 demo 数据有 10 万条记录，最终决赛 100万~1000 万条记录

普通挑战

完成普通挑战的前 50 名选手即可获得参赛纪念奖品
要求选手用一条 SQL 实现乘客分配车次信息，具体要求：
1. 只能用一条 SQL ，可以使用数据库内置函数，不能使用自定义函数和存储过程
2. 每个乘客最多只能分配一个车次
3. 每列火车分配的乘客数不能超过座位数
4. 不能出现火车有空余座位，但对应起始站和终点站的乘客未分配票
5. 如果没有可分配的车次， train_id 返回空值 ( null )
6. 返回结果按 passenger_id 字符顺序排序
返回的结果集示例如下：

进阶挑战

完成普通挑战的选手，可以进一步完成进阶挑战。（注：提交进阶挑战 SQL 前必须先提交普通挑战的 SQL ）
进阶挑战性能排名前8的选手进入决赛成绩评选。

在普通挑战基础上，要求给每个乘客分配具体座位号，每列火车根据座位数有若干个车厢（编号 1~N ），每节车厢有 100 个座位，分 20 排，每排5个座位（ A,B,C,E,F ）,注意：没有编号 D 的座位。
每列火车允许发售 10% 的无座票，无座的乘客 coach_number 为空，seat_number =无座

火车票分配时需要优先分配有座的票，即仅当A地到B地的所有有座票分配完后，才分配无座车票