如何在SQLite中获取事件的上一个/下一个ID

现有事件数据 #

sqlite> select id, date, name from event;
1|2025-04-30|april event
2|2025-05-02|may 2 event
3|2025-05-01|May Day morning
4|2025-05-01|May Day tour

需求说明 #

需实现getPrev()和getNext()逻辑，满足以下规则：

• getPrev(1) 返回 null，getNext(1) 返回 3
• getPrev(2) 返回 4，getNext(2) 返回 null
• getPrev(3) 返回 1，getNext(3) 返回 4
• getPrev(4) 返回 3，getNext(4) 返回 2

注：同一日期下的事件顺序不做强制要求

过往尝试及问题 #

简单SQL语句尝试 #

SELECT 'prev for 3 is:', id, date, name
 FROM event
 WHERE date <= '2025-05-01'
 AND id != 3
 ORDER BY date DESC, id DESC
 LIMIT 1;

SELECT 'next for 3 is:', id, date, name
 FROM event
 WHERE date >= '2025-05-01'
 AND id != 3
 ORDER BY date ASC, id ASC
 LIMIT 1;

执行后出现异常：5月1日的两个事件得到完全相同的上下链接结果：

prev for 3 is:|4|2025-05-01|May Day tour
next for 3 is:|4|2025-05-01|May Day tour

带分子查询的SQL尝试 #

SELECT 'prev for 3 is:', id, date, name FROM (
    SELECT id, date, name
     FROM event
     WHERE date <= '2025-05-01'
     AND id != 3
     ORDER BY date DESC, id DESC
     LIMIT (
        select count(*)
        from event
        where date = '2025-05-01'
    )
)
ORDER BY id DESC
LIMIT 1;

SELECT 'next for 3 is:', id, date, name FROM (
    SELECT id, date, name
     FROM event
     WHERE date >= '2025-05-01'
     AND id != 3
     ORDER BY date ASC, id ASC
     LIMIT (
        select count(*)
        from event
        where date = '2025-05-01'
    )
)
ORDER BY id ASC
LIMIT 1;

对id=4执行后仍得到错误结果：

prev for 3 is:|4|2025-05-01|May Day tour
next for 3 is:|2|2025-05-02|may 2 event -- 此处应为#4 May Day tour
prev for 4 is:|3|2025-05-01|May Day morning
next for 4 is:|2|2025-05-02|may 2 event -- 或此处应为#3 May Day Morning

问题核心 #

ID与日期为独立排序维度，同一日期的事件处于无序集合，无法直接通过where id < ?这类约束区分同一日期内的上下事件。

目前已通过代码实现需求，但希望找到纯SQL的解决方案。

内容的提问来源于stack exchange，提问作者Kevin G.