SQL编程大师李鹏军：凭借高效的SQL策略，让算法设计与代码性能完美融合

select t.passenger_id,t.departure_station,t.arrival_station    ,t.train_id    ,case when t.ticket_status = 1 then CONCAT(t.coach_number,'') else null end coach_number    ,case when t.ticket_status = 1 then t.seat_number when t.ticket_status = 2 then '无座' else null end seat_numberfrom (    select t.passenger_id,t.departure_station,t.arrival_station,t2.train_id        ,case when t.person_Rid between seat_count_all_step_lag + 1 and seat_count_all_step then 1  --有座            when t.person_Rid between seat_count_all + seat_count_all_step_lag/10 + 1 and seat_count_all + seat_count_all_step/10 then 2    --无座            else 3  --无票         end ticket_status        ,CEILING((person_Rid-seat_count_all_step_lag)*1.0/100) coach_number  --车厢号        ,CEILING(((person_Rid-1)%100+1)*1.0/5) row_num   --第几排        ,(person_Rid-1)%5+1 col_num   --第几列（具体的座位（ A,B,C,E,F ））        ,concat(CEILING(((person_Rid-1)%100+1)*1.0/5),case (person_Rid-1)%5+1 when 1 then 'A' when 2 then 'B' when 3 then 'C' when 4 then 'E' when 5 then 'F' end) seat_number     --行列组合的最终座位号    from (        select passenger_id,departure_station,arrival_station            ,ROW_NUMBER() over(partition by departure_station,arrival_station order by passenger_id) person_Rid --相同起点终点的所有乘客进行编号        from passenger    ) t    left join (        select train_id,departure_station,arrival_station,seat_count            ,seat_count_all_step            ,coalesce(lag(seat_count_all_step) over(partition by departure_station,arrival_station order by train_id),0) seat_count_all_step_lag    --相同起点终点的列车，上一车次允许乘坐乘客的结束编号            ,seat_count_all        from (            select train_id,departure_station,arrival_station,seat_count                ,sum(seat_count) over(partition by departure_station,arrival_station order by train_id) seat_count_all_step --相同起点终点的列车从第一车次到当前车次的总座位数，也是当前车次允许乘坐乘客的结束编号                ,sum(seat_count) over(partition by departure_station,arrival_station) seat_count_all --相同起点终点的所有列总的座位数总和            from train        ) t    ) t2 on t.departure_station = t2.departure_station and t.arrival_station = t2.arrival_station and ( t.person_Rid between seat_count_all_step_lag + 1 and seat_count_all_step/*有坐票*/ or t.person_Rid between seat_count_all + seat_count_all_step_lag/10 + 1 and seat_count_all + seat_count_all_step/10/*百分之10的无座票*/ )) torder by t.passenger_id;