如何编写SQL查询返回出现≥2次的EventID且排除其最后一条实例

• 拉取ert_change_log表中出现2次及以上更新实例的EventID对应记录
• 排除上述EventID对应的TIME_OF_EVENT字段值最新的一条记录
• 结果用于核查存在多次更新的事件，可通过比对TIME_OF_EVENT与PREV_ERT字段判断事件是否在更新中途过期

你当前编写的基础条件查询SQL如下，会作为最终查询的前置过滤条件：

select *
from ert_change_log
where time_of_event > '30-SEP-21 23:59:59'
and source <> 'I'

采用窗口函数实现分组计数和排序筛选，兼容MySQL 8.0+、Oracle、PostgreSQL等支持标准SQL窗口函数的数据库：

WITH filtered_data AS (
    SELECT 
        *,
        -- 同一个EventID下按更新时间倒序排序，最新的记录序号为1
        ROW_NUMBER() OVER (PARTITION BY EventID ORDER BY TIME_OF_EVENT DESC) AS rn,
        -- 统计同一个EventID下的总更新记录数
        COUNT(*) OVER (PARTITION BY EventID) AS event_record_count
    FROM ert_change_log
    WHERE time_of_event > '30-SEP-21 23:59:59'
      AND source <> 'I'
)
SELECT * 
FROM filtered_data
-- 筛选出现2次及以上的EventID，且排除最新的一条记录
WHERE event_record_count >= 2 
  AND rn > 1;

逻辑说明 #

• 内层CTE先执行给定的基础过滤条件，同时对每个EventID的记录做分组排序和计数
• 外层筛选条件event_record_count >= 2保证只保留更新次数≥2的EventID
• 外层筛选条件rn > 1自动排除每个EventID下更新时间最新的那条记录
• 按照示例场景，该查询会返回EventID为210043901和210044021除最新更新记录外的所有符合条件的结果

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