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_numberFROM [game_ticket].[passenger] P WITH (NOLOCK)LEFT JOIN result R ON P.passenger_id = R.passenger_idLEFT JOIN to_seat_number T ON R.ans_id = T.ans_idORDER BY P.passenger_id