SQL编程大师张永志：从生成车票到性能优化，数据库大赛代码算法解读！

### Oracle实现：
with 
t_sequence as (
select rownum as seq_no from dual connect by level <= 1600*1.1
),
tmp_seat as (
select train_id,departure_station,arrival_station,coach_number,seat_num, is_seat_flag,
seq_no,row_number() over(partition by departure_station, arrival_station order by is_seat_flag desc, train_id, seq_no) as seat_rn
from (
select t1.train_id, t1.departure_station, t1.arrival_station, 
  1+floor((t2.seq_no-1)/100) as coach_number, 
 concat(1+trunc((t2.seq_no - floor((t2.seq_no-1)/100) * 100 - 1) /5),TRANSLATE(mod(t2.seq_no,5),'12340','ABCEF')) as seat_num,
 t2.seq_no,
 1 as is_seat_flag
 from train t1, t_sequence t2
where t1.seat_count >= t2.seq_no
union all
select t1.train_id, t1.departure_station, t1.arrival_station, NULL as coach_number, '无座' as seat_num, t2.seq_no, 0 as is_seat_flag
 from train t1, t_sequence t2
where t2.seq_no > t1.seat_count and t2.seq_no <= t1.seat_count * 1.1
 ) 
),
tmp_passenger as (
select t1.passenger_id, t1.departure_station, t1.arrival_station,
 row_number() over(partition by t1.departure_station, t1.arrival_station order by t1.passenger_id) as pass_rn
from passenger t1
)
select t1.passenger_id, t1.departure_station, t1.arrival_station, t2.train_id, t2.coach_number,t2.seat_num
from tmp_passenger t1 left join tmp_seat t2 
on t1.departure_station = t2.departure_station
  and t1.arrival_station = t2.arrival_station
  and t2.seat_rn = t1.pass_rn
order by t1.passenger_id;