如何用更简洁的SQL查询航班的始发地与最终目的地？

针对你的需求，这里提供几种更简洁的SQL实现思路，避免原方案中多层CTE和关联的繁琐：

方案一：窗口函数+条件聚合 #

SELECT
    INITCAP(flight) AS flight,
    MAX(CASE WHEN id = min_id THEN source END) AS source,
    MAX(CASE WHEN id = max_id THEN destination END) AS destination
FROM (
    SELECT
        *,
        MIN(id) OVER (PARTITION BY flight) AS min_id,
        MAX(id) OVER (PARTITION BY flight) AS max_id
    FROM source_flight_destination
) t
GROUP BY flight
ORDER BY flight;

思路说明 #

1. 内层子查询通过窗口函数MIN(id)和MAX(id)，直接计算每个航班分组下的最小ID和最大ID
2. 外层通过条件聚合，筛选出最小ID对应的始发地（source）和最大ID对应的最终目的地（destination）
3. 使用INITCAP()统一航班名称的大小写，匹配预期输出格式

方案二：关联子查询 #

SELECT
    INITCAP(s.flight) AS flight,
    s.source,
    d.destination
FROM (
    SELECT flight, source
    FROM source_flight_destination
    WHERE (flight, id) IN (SELECT flight, MIN(id) FROM source_flight_destination GROUP BY flight)
) s
JOIN (
    SELECT flight, destination
    FROM source_flight_destination
    WHERE (flight, id) IN (SELECT flight, MAX(id) FROM source_flight_destination GROUP BY flight)
) d ON s.flight = d.flight
ORDER BY flight;

思路说明 #

1. 两个子查询分别筛选出每个航班最小ID的始发地，以及最大ID的目的地
2. 通过航班名称关联两个子查询的结果，直接得到所需数据
3. 逻辑直观，适合对关联查询更熟悉的场景

方案三：双ROW_NUMBER窗口函数 #

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY flight ORDER BY id ASC) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY flight ORDER BY id DESC) AS rn_desc
    FROM source_flight_destination
)
SELECT
    INITCAP(flight) AS flight,
    MAX(CASE WHEN rn_asc = 1 THEN source END) AS source,
    MAX(CASE WHEN rn_desc = 1 THEN destination END) AS destination
FROM ranked
GROUP BY flight
ORDER BY flight;

思路说明 #

1. 单个CTE中同时计算每个航班按ID升序和降序的行号
2. 升序行号为1的是最早记录（始发地），降序行号为1的是最晚记录（目的地）
3. 条件聚合直接提取对应数据，相比原方案减少了多层CTE和关联操作

内容的提问来源于stack exchange，提问作者vijay shanker