表中“下一步”和“前一个”的SQL是什么？

Question

我有一个项目表，每个项目都有一个与之相关的日期。如果我有与某一项相关联的日期，如何使用SQL查询数据库以获取表中的“前一项”和“后续”项？

不可能简单地添加(或减去)一个值，因为日期之间没有固定的间隔。

一个可能的应用程序是相册或博客web应用程序中的“前一个/下一个”链接，其中底层数据位于SQL表中。

我认为有两种可能的情况：

Firstly，其中每个日期都是唯一的：

样本数据：

1,3,8,19,67,45

当提供8作为参数时，什么样的查询(或查询)会给出3和19？(或第3、8、19行)。注意，并不总是有三行要返回--在序列的末尾，一个将丢失。

其次，如果有一个单独的唯一键来对元素进行排序，那么返回“围绕”日期的集合的查询是什么？预期的订单是日期，然后是键。

样本数据：

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8

对“8”的查询返回集合：

2:3,3:8,4:8,16:8,5:19

或生成表的查询：

key date prev-key next-key
1   1    null     2
2   3    1        3
3   8    2        4
4   8    3        16
5   19   16       10
10  19   5        11
11  67   10       15
15  45   11       null
16  8    4        5

表顺序并不重要，只是下一个键和prev键字段。

TheSoftwareJedi和Cade都有适用于我昨晚发布的数据集的解决方案。对于第二个问题，对于这个数据集，两者似乎都失败了：

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8

预期的顺序是按日期然后按键，因此一个预期的结果可能是：

2:3,3:8,4:8,16:8,5:19

另一个：

key date prev-key next-key
1   1    null     2
2   3    1        3
3   8    2        4
4   8    3        16
5   19   16       10
10  19   5        11
11  67   10       15
15  45   11       null
16  8    4        5

表顺序并不重要，只是下一个键和prev键字段。

Answer 1

我自己尝试基于TheSoftwareJedi的set解决方案。

第一个问题：

select date from test where date = 8
union all
select max(date) from test where date < 8
union all
select min(date) from test where date > 8
order by date;

第二个问题：

在调试时，我使用了数据集：

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8,17:3,18:1

为了给出这个结果：

select * from test2 where date = 8
union all
select * from (select * from test2
                   where date = (select max(date) from test2 
                                     where date < 8)) 
    where key = (select max(key) from test2 
                    where date = (select max(date) from test2 
                                      where date < 8))
union all
select * from (select * from test2
                   where date = (select min(date) from test2 
                                     where date > 8)) 
    where key = (select min(key) from test2 
                    where date = (select min(date) from test2 
                                      where date > 8))
order by date,key;

在这两种情况下，最终的order子句严格来说是可选的。

Answer 2

从元素<8的数据中选择max(元素)

友联市

从元素>8的数据中选择min(元素)

但是通常，将sql用于面向集合的操作而不是迭代操作更有用。

Answer 3

自我连接。

表中：

/*
CREATE TABLE [dbo].[stackoverflow_203302](
    [val] [int] NOT NULL
) ON [PRIMARY]
*/

带参数@val

SELECT cur.val, MAX(prv.val) AS prv_val, MIN(nxt.val) AS nxt_val
FROM stackoverflow_203302 AS cur
LEFT JOIN stackoverflow_203302 AS prv
    ON cur.val > prv.val
LEFT JOIN stackoverflow_203302 AS nxt
    ON cur.val < nxt.val
WHERE cur.val = @val
GROUP BY cur.val

您可以使用输出参数使其成为一个存储过程，也可以将其作为一个关联的子查询加入到您正在提取的数据中。

如果没有该参数，对于您的数据，结果将是：

val         prv_val     nxt_val
----------- ----------- -----------
1           NULL        3
3           1           8
8           3           19
19          8           45
45          19          67
67          45          NULL

对于修改后的示例，可以将其用作关联子查询：

/*
CREATE TABLE [dbo].[stackoverflow_203302](
    [ky] [int] NOT NULL,
    [val] [int] NOT NULL,
    CONSTRAINT [PK_stackoverflow_203302] PRIMARY KEY CLUSTERED (
        [ky] ASC
    )
)
*/

SELECT cur.ky AS cur_ky
        ,cur.val AS cur_val
        ,prv.ky AS prv_ky
        ,prv.val AS prv_val
        ,nxt.ky AS nxt_ky
        ,nxt.val as nxt_val
FROM (
    SELECT cur.ky, MAX(prv.ky) AS prv_ky, MIN(nxt.ky) AS nxt_ky
    FROM stackoverflow_203302 AS cur
    LEFT JOIN stackoverflow_203302 AS prv
        ON cur.ky > prv.ky
    LEFT JOIN stackoverflow_203302 AS nxt
        ON cur.ky < nxt.ky
    GROUP BY cur.ky
) AS ordering
INNER JOIN stackoverflow_203302 as cur
    ON cur.ky = ordering.ky
LEFT JOIN stackoverflow_203302 as prv
    ON prv.ky = ordering.prv_ky
LEFT JOIN stackoverflow_203302 as nxt
    ON nxt.ky = ordering.nxt_ky

预期的产出如下：

cur_ky      cur_val     prv_ky      prv_val     nxt_ky      nxt_val
----------- ----------- ----------- ----------- ----------- -----------
1           1           NULL        NULL        2           3
2           3           1           1           3           8
3           8           2           3           4           19
4           19          3           8           5           67
5           67          4           19          6           45
6           45          5           67          NULL        NULL

在Server中，我更喜欢将子查询作为公共表表达式。这使得代码看起来更线性，嵌套更少，并且如果有很多嵌套，代码就更容易理解(另外，在某些重新连接上需要更少的重复)。