如何通过条件化SQL GROUP BY筛选符合预订要求的唯一用户

嘿，我来帮你解决这个查询需求！要找出那些最近3条状态为completed的预订记录里notified全为NULL的用户，我们可以用窗口函数结合分组筛选来实现，具体方案如下：

我们需要同时满足两个关键条件：

• 用户至少有3条状态为completed的预订记录（如果不足3条，自然不符合“最近3条都满足”的要求）
• 这3条最新的completed记录中，notified字段全部为NULL

SELECT u.id, u.name
FROM Users u
JOIN (
    -- 子查询：为每个用户的completed记录按时间倒序排名（这里假设id越大记录越新，可替换为时间字段）
    SELECT 
        user_id,
        notified,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) AS record_rank
    FROM Bookings
    WHERE state = 'completed'
) ranked_bookings ON u.id = ranked_bookings.user_id
WHERE ranked_bookings.record_rank <= 3  -- 仅保留每个用户最近的3条completed记录
GROUP BY u.id, u.name
HAVING 
    -- 确保这3条记录里没有notified非NULL的情况
    COUNT(CASE WHEN ranked_bookings.notified IS NOT NULL THEN 1 END) = 0
    -- 确保用户确实有至少3条completed记录（避免不足3条的用户被误选）
    AND COUNT(*) = 3;

1. 子查询部分：使用ROW_NUMBER()窗口函数，按用户ID分组，将每个用户的completed记录按ID倒序（如果你的表有创建时间字段，比如created_at，建议替换为ORDER BY created_at DESC，这样时间排序更准确）分配排名，最新的记录排名为1，次新为2，以此类推。
2. 主查询关联：将用户表和排名后的预订记录关联，只筛选出每个用户排名前3的记录。
3. 分组筛选：通过GROUP BY聚合用户数据后，用HAVING子句做两个校验：
   • 统计这3条记录中notified非NULL的数量为0，确保所有记录的notified都是NULL
   • 统计记录总数为3，确保用户至少有3条符合条件的预订记录

如果你的业务场景中，用户可能有超过3条completed记录，但我们只关心最近3条的状态，这个查询就能精准定位到符合要求的用户。

内容的提问来源于stack exchange，提问作者Thiago Augustus Oliveira