SQL编程大师-傅昌林：利用 SQL Server 构建临时表与窗口函数，2.702 秒优化百万火车票分配

;WITH series_gen_cte AS (
    SELECT 0 AS x
    UNION ALL
    SELECT x + 1 FROM series_gen_cte WHERE x < 99
), to_seat_number AS (
 SELECT
  x as ans_id,
  CAST(X/5 + 1 as nvarchar) +
  CASE X%5 
   WHEN 0 THEN 'A' 
   WHEN 1 THEN 'B' 
   WHEN 2 THEN 'C' 
   WHEN 3 THEN 'E' 
   WHEN 4 THEN 'F' 
  END as seat_number
 FROM series_gen_cte
 UNION
 SELECT 100, N'无座'
), station_to_id AS (
 SELECT S.station_name, ROW_NUMBER() OVER(ORDER BY S.station_name) as station_id
 FROM (
  SELECT distinct [departure_station] as station_name
  FROM [game_ticket].[train]
  UNION
  SELECT distinct [arrival_station] 
  FROM [game_ticket].[train]
 ) S
), train_with_empty AS (
 SELECT
  S1.station_id * 10000 + S2.station_id as route_id,
  [train_id],
  [seat_count],
  0 as seat_type
 FROM [game_ticket].[train] T
 JOIN station_to_id S1 ON T.departure_station = S1.station_name
 JOIN station_to_id S2 ON T.arrival_station = S2.station_name 
 UNION ALL
 SELECT
  S1.station_id * 10000 + S2.station_id as route_id,
  [train_id],
  [seat_count] / 10,
  1 as seat_type
 FROM [game_ticket].[train] T
 JOIN station_to_id S1 ON T.departure_station = S1.station_name
 JOIN station_to_id S2 ON T.arrival_station = S2.station_name 
), route_info AS (
 SELECT
  route_id,
  train_id,
  seat_type,
  seat_count,
  SUM([seat_count]) OVER (PARTITION BY route_id ORDER BY route_id, seat_type, seat_count, train_id) as running_seat_count
 FROM train_with_empty T
), passengers_in_scope AS (
 SELECT 
  P.passenger_id, 
  S1.station_id * 10000 + S2.station_id as route_id,
  COUNT(1) OVER (PARTITION BY S1.station_id, S2.station_id ORDER BY P.passenger_id) as passenger_rid
 FROM [game_ticket].[passenger] P WITH (NOLOCK)
 JOIN station_to_id S1 ON P.departure_station = S1.station_name
 JOIN station_to_id S2 ON P.arrival_station = S2.station_name
), result AS (
 SELECT
  S.passenger_id,
  R.train_id,
  CASE
   WHEN R.seat_type = 1 THEN NULL
   ELSE 1 + (S.passenger_rid - R.running_seat_count + R.seat_count - 1) / 100
  END as coach_number,
  CASE 
   WHEN R.seat_type = 1 THEN 100
   ELSE (S.passenger_rid - R.running_seat_count + R.seat_count - 1) % 100
  END as ans_id
 FROM passengers_in_scope S
 JOIN route_info R
  ON S.route_id = R.route_id 
  AND S.passenger_rid > R.running_seat_count - R.seat_count 
  AND S.passenger_rid <= R.running_seat_count
)
SELECT 
 P.passenger_id,
 P.departure_station,
 P.arrival_station, 
 R.train_id, 
 R.coach_number,
 T.seat_number
FROM [game_ticket].[passenger] P WITH (NOLOCK)
LEFT JOIN result R ON P.passenger_id = R.passenger_id
LEFT JOIN to_seat_number T ON R.ans_id = T.ans_id
ORDER BY P.passenger_id