如何编写SQL查询获取字段倒数第二次出现的对应记录

核心实现逻辑是将窗口函数的排序规则改为按出现时间倒序，即可将每个personID分组内的倒数第N条记录转换为固定行号N的筛选条件，不需要适配不同分组的总记录数差异。

正确查询语句如下：

SELECT seenID, personID, seenTime FROM 
(
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY personID ORDER BY seenTime DESC) AS reverse_row_num,
        * 
    FROM "YourTableName"
) AS T
WHERE reverse_row_num = 2

说明 #

1. 原写法中窗口函数排序字段选择personID属于逻辑错误，分组内排序规则不稳定，必须按业务维度的seenTime排序才能匹配出现顺序的需求
2. 倒序排序后，每个分组内最新的记录行号为1，倒数第二行行号固定为2，自动适配不同personID的总出现次数
3. 仅出现过1次的personID不会出现在结果中，因为不存在行号为2的记录
4. 如果仅需要查询特定personID的结果，直接在外层WHERE添加personID = 3即可得到你给出的预期输出

如果需要用正序排序逻辑实现，也可以用分组计数匹配的写法，效果完全一致：

SELECT seenID, personID, seenTime FROM 
(
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY personID ORDER BY seenTime ASC) AS row_num,
        COUNT(*) OVER(PARTITION BY personID) AS total_cnt,
        * 
    FROM "YourTableName"
) AS T
WHERE row_num = total_cnt - 1

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