编写SQL查询筛选最新事件为13/14且未触发过15/16状态的ID

需求规则 #

• 目标ID的最新事件满足condition1 = 13且condition2 = 14
• 排除所有曾出现过condition1 = 15且condition2 = 16记录的ID：即使该ID最新事件为13/14，只要历史触发过15/16状态，也不纳入结果

实现思路 #

1. 先提取所有曾触发过15/16状态的ID列表，作为排除集合
2. 用窗口函数为每个ID的所有事件按时间倒序排序，取排序为1的即为每个ID的最新事件
3. 过滤出最新事件满足13/14、且ID不在排除集合内的记录即可

参考SQL代码 #

WITH exclude_ids AS (
    -- 筛选所有曾出现15/16状态的ID
    SELECT DISTINCT id 
    FROM event_table -- 替换为实际表名
    WHERE condition1 = 15 AND condition2 = 16
),
latest_event AS (
    -- 给每个ID的记录按时间倒序排序，取rn=1即为最新事件
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY `datetime` DESC) AS rn
    FROM event_table -- 替换为实际表名
)
SELECT id, `datetime`, `date`, condition1, condition2
FROM latest_event
WHERE rn = 1
  AND condition1 = 13 AND condition2 = 14
  AND id NOT IN (SELECT id FROM exclude_ids)

结果说明 #

针对给出的示例数据，最终仅ID为105的记录符合要求，和期望输出一致。

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