⓵悟！从12306改签困惑到数据库设计——高铁随记

梁敬彬梁敬弘兄弟出品

1. 故事从L老师进京买车票说起

L老师准备去北京参加DTCC中国数据库大会。

于是预订了从福州到北京的G302次高铁票，后来临时有事，尝试将G302（7:23出发）改签到G322（9:14出发）

结果发现系统提示G322"售罄"，无法完成改签。

2. L老师分段分配新座的需求

接下来我们做一个需求分析。

为便于分析，我们把福州到北京的路线简化为只分四段，路线：福州 → 杭州 → 南京 → 北京，同时把车厢也限制在仅有10号车厢。

心有不甘的L老师经过查询，发现了一些玄机，

10车13F：福州至杭州空闲，杭州至南京已售，南京至北京空闲

10车07C：福州至杭州已售，杭州至南京空闲，南京至北京空闲

发现玄机了，实际上是有可行的座位组合的：

福州到杭州坐10车13F

杭州到南京换到10车07C

南京到北京可以回到10车13F或继续坐10车07C

这意味着，如果系统支持分段分配新座位，是可以完成全程订票的。然而，现有系统只能查询和分配全程相同的座位，无法识别这种分段组合的可能性。于是L老师放弃了，继续乘坐原来的G302（7:23出发）。

当然了，理论上L老师是可以买一张福州到杭州的车票，再购买杭州到北京的车，不过L老师觉得麻烦，也就算了。其实如果12306允许自动给乘客分段分配座位（即到不同的站换不同的座位），问题就可以解决。那该怎么实现呢？

3. 最简模型设计与SQL实现

我们可以做一个最小化的例子来示例说明一下该如何实现，以下是最简化的设计说明，实际情况当然远比这要复杂的多！

首先，创建表：

CREATE TABLE TRAIN_SEATS (
    SEAT_ID NUMBER PRIMARY KEY,
    TRAIN_ID VARCHAR2(10),
    CAR_NO NUMBER,
    SEAT_NO VARCHAR2(3),
    SEGMENT NUMBER,
    STATUS VARCHAR2(10)
);

插入示例数据：

INSERT INTO TRAIN_SEATS VALUES (1, 'G322', 10, '13F', 1, 'AVAILABLE');
INSERT INTO TRAIN_SEATS VALUES (2, 'G322', 10, '13F', 2, 'OCCUPIED');
INSERT INTO TRAIN_SEATS VALUES (3, 'G322', 10, '13F', 3, 'AVAILABLE');
INSERT INTO TRAIN_SEATS VALUES (4, 'G322', 10, '07C', 1, 'OCCUPIED');
INSERT INTO TRAIN_SEATS VALUES (5, 'G322', 10, '07C', 2, 'AVAILABLE');
INSERT INTO TRAIN_SEATS VALUES (6, 'G322', 10, '07C', 3, 'AVAILABLE');

以下是SQL实现的代码，具体如下。

WITH AVAILABLE_SEATS AS (
    SELECT 
        CAR_NO,              -- 车厢号
        SEAT_NO,             -- 座位号
        SEGMENT,             -- 行程段（1：福州到杭州，2：杭州到南京，3：南京到北京）
        STATUS,              -- 座位状态
        ROW_NUMBER() OVER (
            PARTITION BY SEGMENT 
            ORDER BY CAR_NO, SEAT_NO
        ) AS RN               -- 为每个行程段的可用座位分配一个唯一的行号
    FROM TRAIN_SEATS
    WHERE TRAIN_ID = 'G322'   -- 只选择G322列车的座位
    AND STATUS = 'AVAILABLE'  -- 只选择可用的座位
)
SELECT 
    CASE 
        -- 如果三个行程段都有可用座位，则可以全程乘坐
        WHEN COUNT(DISTINCT SEGMENT) = 3 THEN '可以全程乘坐'
        -- 如果至少有一个行程段有可用座位，则需要换座
        WHEN COUNT(DISTINCT SEGMENT) >= 1 THEN '需要换座'
        -- 如果没有任何可用座位，则无法完成行程
        ELSE '无法完成行程'
    END AS TRAVEL_PLAN,
    -- 使用LISTAGG函数将所有可用座位信息连接成一个字符串
    LISTAGG('段' || SEGMENT || ':' || CAR_NO || '车' || SEAT_NO || '座', ' -> ') 
    WITHIN GROUP (ORDER BY SEGMENT) AS SEAT_ARRANGEMENT
FROM AVAILABLE_SEATS
WHERE RN = 1  -- 只选择每个行程段的第一个可用座位
GROUP BY RN;  -- 由于RN总是1，这里的GROUP BY实际上是将所有结果合并成一行

SQL的详细说明：

1. AVAILABLE_SEATS 子查询：

从TRAIN_SEATS表中选择G322列车的所有可用座位。

使用ROW_NUMBER()函数为每个行程段的可用座位分配一个唯一的行号。这允许我们后续只选择每个段的第一个可用座位。

2. 主查询：

CASE语句用于确定旅行计划：

如果所有3个段都有可用座位，则可以全程乘坐。 如果至少有1个段有可用座位，则需要换座。 如果没有任何可用座位，则无法完成行程。
LISTAGG函数用于生成一个字符串，描述每个段的可用座位。

WHERE RN = 1 确保我们只选择每个段的第一个可用座位。

GROUP BY RN 将结果合并成一行，因为RN总是1。

这个查询有效地找出了允许换座情况下的最佳座位组合，展示了如果系统支持分段座位分配，是可以完成全程订票的。

具体实现结果如下所示：

TRAVEL_PLAN | SEAT_ARRANGEMENT
------------+--------------------------------------------------
需要换座     | 段1:10车13F座 -> 段2:10车07C座 -> 段3:10车07C座

这个结果准确地反映了L老师的分析：

福州到杭州（段1）坐10车13F

杭州到南京（段2）换到10车07C

南京到北京（段3）继续坐10车07C

这个查询展示了如果系统支持分段座位分配，是可以完成全程订票的。

看来打破现有系统（只能查询和分配全程相同的座位）的局限性，允许分段换座在理论上是比较容易实现的。

4. 延伸&花絮

如果有人嫌这种方式麻烦，其实铁路部门还是可以考虑对分段乘坐做一个折扣优惠。这样看在钱的份上，大家也不会嫌弃中途换座位的麻烦。当然了，在春运这种一票难求的时候，就不用考虑折扣了，能多送一个乘客回家团圆，那可是功德无量的事。

在动车上做试验，写的SQL脚本是Oracle语法，结果写完脚本后发现自己的Oracle测试环境连不上，咋办，不验证一下脚本正确与否心里不踏实啊。忽然想起了达梦数据库和Oracle兼容性很高，于是给达梦的老朋友黄海明老师打了个电话求救，结果他给了一个免费的在线达梦环境，于是我测试了一下，一次性输出正确的我要的结果，看来达梦数据库对Oracle的兼容性名不虚传啊。

12306如果支持分段分座，不仅能造福百姓，也能为自己创收，到底会不会实现呢？

未完待续…
⓶惊！12306业务系统升级也能被普通人推动？

公众号：收获不止数据库

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列