How to Allocate Train Seats Across Segments Using SQL

Story Background

L plans to attend the DTCC conference in Beijing and initially books a G302 high‑speed train ticket from Fuzhou to Beijing on the 21st. When trying to change to G322, the system reports the train is sold out, so the change fails.

Requirement Analysis

To explore possible solutions, the route is simplified into four segments: Fuzhou → Hangzhou → Nanjing → Beijing, and only carriage 10 is considered. Seat availability for carriage 10 is collected:

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

Carriage 10, seat 07C: sold Fuzhou‑Hangzhou, free Hangzhou‑Nanjing, free Nanjing‑Beijing.

These data show a feasible combination of seats across the three segments.

Problem Statement

The existing ticketing system can only query and assign a single seat for the whole journey; it cannot recognise that different segments may require different seats. Therefore L gives up the change and keeps the original G302 ticket.

Minimal Model Design and SQL Implementation

A tiny example demonstrates how to achieve segment‑wise seat allocation.

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 sample data:

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 core query uses a CTE to list available seats, assigns a row number per segment, and then decides the travel plan:

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 of the Query

The AVAILABLE_SEATS sub‑query selects all free seats for train G322 and numbers them per segment.

The outer CASE determines whether all three segments have a free seat, at least one segment has a free seat, or none have. LISTAGG concatenates the chosen seats into a readable string.

Filtering on RN = 1 ensures only the first available seat of each segment is used.

Result

The query returns:

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

This matches L's manual analysis: take seat 13F for Fuzhou‑Hangzhou, switch to 07C for Hangzhou‑Nanjing, and stay on 07C for Nanjing‑Beijing.

Conclusion

The demonstration shows that if the ticketing system supported segment‑wise seat allocation, a passenger could complete the whole journey even when a single‑segment ticket is unavailable. The approach is technically straightforward and can be extended to real‑world systems.

Extension & Anecdote

In practice, rail operators could offer discount incentives for passengers who accept seat changes, though during peak travel periods the priority is simply getting more passengers home. The author also notes that the Oracle‑compatible DM database successfully executed the script, confirming its compatibility.