MySQL高阶2112-最繁忙的机场

题目

编写一个 SQL 来查询 流量最大 的机场的 ID。客流量最大的机场是指从该机场起飞或抵达该机场的航班总数最多的机场。如果有多个机场流量最大，请全部查询出来。

准备数据

Create table If Not Exists Flights (departure_airport int, arrival_airport int, flights_count int)
    Truncate table Flights
    insert into Flights (departure_airport, arrival_airport, flights_count) values ('1', '2', '4')
    insert into Flights (departure_airport, arrival_airport, flights_count) values ('2', '1', '5')
    insert into Flights (departure_airport, arrival_airport, flights_count) values ('2', '4', '5')

 分析数据

1 号机场有 9 个航班 (4 个出发, 5 个到达).
2 号机场有 14 个航班 (10 个出发, 4 个到达).
4 号机场有 5 个航班 (5 个到达).
客流量最大的机场是 2 号机场

实现数据

第一步:双向选择使用union all实现列转行

select departure_airport,flights_count from flights
union all
select arrival_airport,flights_count from flights;

第二步:根据机场进行航班求和

with t1 as (
    select departure_airport,flights_count from flights
    union all
    select arrival_airport,flights_count from flights
)select departure_airport,sum(flights_count) cn from t1
group by departure_airport;

第三步:选出最多航班的机场

with t2 as (
    with t1 as (
        select departure_airport, flights_count from flights
        union all
        select arrival_airport as departure_airport, flights_count from flights
    )
    select departure_airport, sum(flights_count) as cn from t1
    group by departure_airport
)
select departure_airport as airport_id
from t2
where cn = (select max(cn) from t2);

总计

遇见双向选择的情况,可以使用union all实现行转列