Enabling In‑Train Seat Swaps on 12306: A Practical SQL Design

Background

L teacher booked a G302 high‑speed train from Fuzhou to Beijing for a DTCC conference, but needed to change to G322. The system reported the target train as "sold out" and the change could not be completed, leaving the original itinerary unchanged.

New In‑Train Seat Change Feature

Later, 12306 announced support for "car‑internal seat change" (车内换座), allowing passengers to switch seats within the same train for different journey segments. Users expressed excitement, describing the feature as advanced, user‑friendly, and a way to better utilize train capacity, though some still hoped for ticket supplements during peak travel periods.

Users praised the feature as "highly advanced and humane" and appreciated the potential to maximize train capacity.

Technical Implementation

The core of the solution is a SQL query that treats a full‑journey ticket as a series of segments, each with its own seat availability. The query uses a CTE to list available seats per segment, assigns a row number per segment, and then determines whether a passenger can travel the whole route, needs a seat swap, or cannot complete the journey. The final result aggregates seat information for each segment.

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;

Impact and Feedback

After publishing the article, the author received positive feedback from 12306 technical experts at the DTCC conference, who acknowledged the proposal and expressed willingness to implement the feature. The discussion highlighted both the technical feasibility and the potential user benefits.

Reflections and Recommendations

The author suggests that if passengers find the seat‑swap process cumbersome, offering discount incentives could increase acceptance, though during peak travel periods such discounts may be unnecessary. The article also emphasizes the value of sharing technical solutions, engaging with industry communities, and providing concrete, implementable ideas rather than merely complaining.