SQL查询求助：筛选航班延误1-5分钟且邮编重复的用户

问题核心 #

你需要筛选出两类条件同时满足的用户：

1. 该用户的航班出发延误时间在1-5分钟之间；
2. 该用户所属的邮编，至少有两个不同用户的航班延误都符合1-5分钟的要求（原查询错误地包含了“邮编总用户数>1，但仅单个用户符合延误条件”的记录，优化后的查询又因分组丢失了用户姓名）。

原查询错误分析 #

• 初始查询的子查询仅筛选了整个user表中用户数>1的邮编，但未验证这些邮编下是否有至少两个用户的航班延误符合条件，导致误判。
• 优化后的查询通过GROUP BY postalcode+HAVING COUNT>1强行过滤，但SELECT子句中的u.name未参与分组，数据库会随机返回每个邮编的一个用户，直接丢失了其他符合条件的用户姓名。

正确解决方案 #

方案1：窗口函数实现（推荐） #

利用窗口函数在不分组的前提下，统计每个邮编下符合延误条件的用户数量，再筛选出数量>1的记录，完整保留所有符合条件的用户信息：

SELECT name, postalcode
FROM (
    SELECT 
        u.name, 
        u.postalcode,
        -- 按邮编分组，统计该邮编下符合延误条件的用户数
        COUNT(DISTINCT u.userid) OVER (PARTITION BY u.postalcode) AS qualified_user_count
    FROM user u
    JOIN userscan s ON u.userid = s.userid
    JOIN flightdetails f ON s.ticketid = f.ticketid
    WHERE f.deppdelay > 1 AND f.deppdelay < 5
) AS temp
WHERE qualified_user_count > 1
ORDER BY postalcode;

方案2：子查询筛选目标邮编 #

先通过子查询找出“至少有两个用户符合延误条件”的邮编，再关联查询这些邮编下的用户：

SELECT u.name, u.postalcode
FROM user u
JOIN userscan s ON u.userid = s.userid
JOIN flightdetails f ON s.ticketid = f.ticketid
WHERE f.deppdelay > 1 AND f.deppdelay < 5
AND u.postalcode IN (
    SELECT u_inner.postalcode
    FROM user u_inner
    JOIN userscan s_inner ON u_inner.userid = s_inner.userid
    JOIN flightdetails f_inner ON s_inner.ticketid = f_inner.ticketid
    WHERE f_inner.deppdelay > 1 AND f_inner.deppdelay < 5
    GROUP BY u_inner.postalcode
    HAVING COUNT(DISTINCT u_inner.userid) > 1
)
ORDER BY postalcode;

示例数据验证 #

用你提供的示例数据测试时，两个用户的邮编均为238859且航班延误都在1-5分钟范围内，两个方案都会返回：

若其中一个用户的航班延误超出1-5分钟范围，两个方案都会自动排除该邮编下的所有用户，符合你的需求。

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