如何在Athena中复用SQL查询结果查找重复飞机应答机代码

可以通过**公共表表达式（CTE）**封装你第一步的查询结果，再结合自连接逻辑实现需求，具体SQL如下：

WITH current_aircraft AS (
    SELECT 
        acchl.is_current,
        acchl.aircraft_registration_number,
        acchl.aircraft_transponder_code
    FROM fleets.aircraft_all_history_latest acchl
    WHERE acchl.is_current = true
)
SELECT 
    ca.aircraft_registration_number,
    ca.aircraft_transponder_code
FROM current_aircraft ca
JOIN (
    SELECT aircraft_transponder_code
    FROM current_aircraft
    GROUP BY aircraft_transponder_code
    HAVING COUNT(*) > 1
) dup_codes ON dup_codes.aircraft_transponder_code = ca.aircraft_transponder_code
ORDER BY ca.aircraft_transponder_code, ca.aircraft_registration_number;

逻辑说明： #

1. CTE部分：current_aircraft 先筛选出所有当前有效的飞机数据，复用你第一步的查询逻辑，避免重复编写SQL。
2. 子查询dup_codes：从有效飞机数据中统计出出现次数大于1的应答机码，即存在重复的目标码。
3. 关联查询：将有效飞机数据和重复应答机码列表关联，得到所有拥有重复应答机码的飞机注册号及对应码，最后按应答机码排序方便查看分组。

如果你的数据库不支持CTE，也可以用嵌套子查询实现，效果一致：

SELECT 
    ca.aircraft_registration_number,
    ca.aircraft_transponder_code
FROM (
    SELECT 
        acchl.is_current,
        acchl.aircraft_registration_number,
        acchl.aircraft_transponder_code
    FROM fleets.aircraft_all_history_latest acchl
    WHERE acchl.is_current = true
) ca
JOIN (
    SELECT aircraft_transponder_code
    FROM (
        SELECT 
            acchl.aircraft_transponder_code
        FROM fleets.aircraft_all_history_latest acchl
        WHERE acchl.is_current = true
    ) sub
    GROUP BY aircraft_transponder_code
    HAVING COUNT(*) > 1
) dup_codes ON dup_codes.aircraft_transponder_code = ca.aircraft_transponder_code
ORDER BY ca.aircraft_transponder_code, ca.aircraft_registration_number;

内容的提问来源于stack exchange，提问作者Mark k