mysql - 标准 SQL 以及上一行和下一行值

Question

我知道这是一个常见问题，并且我已经阅读了一些相关内容。我想要的是一种基于引用行 ID 接收下一个和上一个行 ID 的高性能方法(在一个查询中最好)。我在 stackoverflow 上找到了很多问题和答案，还有一个有值(value)的帖子，给出了一个非常好的答案 https://stackoverflow.com/a/15992856/1230358 。我所得到的基于此线程中的答案。

select id from test_1
where ( 
    id = IFNULL((select max(id) from test_1 where id < 2 order by starts_on, id), 0)
    or id = IFNULL((select min(id) from test_1 where id > 2 order by starts_on, id), 0) 
)

使用引用id=2查询返回的结果正是我需要的(第一行是前一个id，第二行是下一个id):

id
--
1
--
3

问题是，如果查询边缘情况 id=1 或 id=max(id)，结果会错过之前的情况或下一行 id，因为根本没有上一行或下一行。结果现在只有一行，不清楚这是否是我们的下一行 id。

id
--
2       (next value)

但是，我需要这样的结果

id
--
NULL    (or 0 - previous value)
--
2       (next value)

我需要的是一个基于或类似于上层查询性能的解决方案，最好用 NULL 值(或 0)填充不存在的边缘情况 id。由于我正在使用支持不同 dbms 的网络框架来计算结果，因此它应该与 mysql、sqlite 和 postgres 一起使用。强>。它应该适用于以下架构:

drop table if exists test_1;
create table test_1 (id INTEGER PRIMARY KEY,starts_on DATETIME, ends_on DATETIME);
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');
insert into test_1 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-01 00:00:00');

drop table if exists test_2;
create table test_2 (id INTEGER PRIMARY KEY,starts_on DATETIME, ends_on DATETIME);
insert into test_2 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-07 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-02 00:00:00', '2017-01-08 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-03 00:00:00', '2017-01-09 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-04 00:00:00', '2017-01-10 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-05 00:00:00', '2017-01-11 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-06 00:00:00', '2017-01-12 00:00:00');
insert into test_2 (starts_on, ends_on) Values ('2017-01-07 00:00:00', '2017-01-13 00:00:00');


drop table if exists test_3;
create table test_3 (id INTEGER PRIMARY KEY,starts_on DATETIME, ends_on DATETIME);
insert into test_3 (starts_on, ends_on) Values ('2017-01-01 00:00:00', '2017-01-07 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-02 00:00:00', '2017-01-08 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-02 00:00:00', '2017-01-09 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-04 00:00:00', '2017-01-10 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-05 00:00:00', '2017-01-11 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-07 00:00:00', '2017-01-12 00:00:00');
insert into test_3 (starts_on, ends_on) Values ('2017-01-07 00:00:00', '2017-01-13 00:00:00');

更新:

一个可能的解决方案是:

select distinct 
(select max(id) from test_1 where id < 7 order by starts_on, id) as prev, 
(select min(id) from test_1 where id > 7 order by starts_on, id) as next 
from test_1

Answer 1

Postgresql Window Functions

select
    lag(id) over (order by starts_on) as previous,
    lead(id) over (order by starts_on) as next
from test_1
where id = 2