Segment‑wise Train Seat Allocation with SQL: A Practical Guide

Background Story

A month ago the author planned to travel from Fuzhou to Beijing for a tech conference. After booking a G302 high‑speed train ticket, a last‑minute change required switching to G322, but the system reported the seat as sold out.

The author discovered that different seat‑car combinations were available on each segment of the route (Fuzhou‑Hangzhou, Hangzhou‑Nanjing, Nanjing‑Beijing), suggesting a feasible “segment‑wise seat allocation” strategy.

Requirement Analysis

To illustrate the idea, the route is simplified into four cities (Fuzhou → Hangzhou → Nanjing → Beijing) and limited to carriage 10. Seat availability per segment is:

Car 10, seat 13F: free on Fuzhou‑Hangzhou, sold on Hangzhou‑Nanjing, free on Nanjing‑Beijing.

Car 10, seat 07C: sold on Fuzhou‑Hangzhou, free on the other two segments.

Thus a viable combination exists: use 13F for segment 1, switch to 07C for segment 2, and stay on 07C for segment 3.

Minimal Database Model and SQL Implementation

The following minimal schema stores seat status for each train, carriage, seat number, and travel segment.

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)
);

Sample data reflecting the availability described above:

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');

The query below finds the first available seat for each segment, determines whether a full‑journey ride is possible, and concatenates the seat assignments.

WITH AVAILABLE_SEATS AS (
    SELECT CAR_NO,
           SEAT_NO,
           SEGMENT,
           STATUS,
           ROW_NUMBER() OVER (PARTITION BY SEGMENT ORDER BY CAR_NO, SEAT_NO) AS RN
    FROM TRAIN_SEATS
    WHERE TRAIN_ID = 'G322' AND STATUS = 'AVAILABLE'
)
SELECT CASE
           WHEN COUNT(DISTINCT SEGMENT) = 3 THEN '可以全程乘坐'
           WHEN COUNT(DISTINCT SEGMENT) >= 1 THEN '需要换座'
           ELSE '无法完成行程'
       END AS TRAVEL_PLAN,
       LISTAGG('段' || SEGMENT || ':' || CAR_NO || '车' || SEAT_NO || '座', ' -> ') WITHIN GROUP (ORDER BY SEGMENT) AS SEAT_ARRANGEMENT
FROM AVAILABLE_SEATS
WHERE RN = 1
GROUP BY RN;

Explanation:

AVAILABLE_SEATS selects all free seats for the specified train and assigns a row number per segment.

The outer query uses a CASE statement to decide the travel plan based on how many segments have available seats. LISTAGG builds a readable string of the chosen seats.

Filtering RN = 1 ensures only the first free seat per segment is taken.

Result example:

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

This output matches the earlier manual analysis: segment 1 uses seat 13F, segment 2 switches to 07C, and segment 3 stays on 07C.

Real‑World Impact

After the article was published, the 12306 railway ticketing system introduced a “in‑train seat change” feature that allows passengers to purchase tickets for different seats on the same train without alighting. The author confirmed the feature’s rollout with a 12306 technical expert.

Takeaways

The case demonstrates how a modest SQL query can solve a practical booking problem when the underlying system supports segment‑wise seat allocation. It also shows that sharing concrete technical solutions can influence product improvements and inspire broader community engagement.