如何获得目标行的prev和下一行

Question

例如，这样的数据

ID  name
8   apple 
13  lemon
15  melon
16  banana
18  watermelon
19  peanuts
20  orange
24  hamburger
25  maron

我是凭身份证拿到香蕉的。

Select id,name where id = 16

不过，我也想得到州长和下一排。

在这种情况下。

我想要15个瓜和18个西瓜。

这方面的最佳做法是什么？

Answer 1

使用Server 2012+，您可以找到LEAD()和LAG() (这里有一个与另一个链接的详细信息)。

较低版本的一种方法可能是：

首先，我将您的数据填充到一个声明的表变量(模拟表)中。

DECLARE @tbl TABLE(ID INT,name VARCHAR(100));
INSERT INTO @tbl VALUES
 (8,'apple') 
,(13,'lemon')
,(15,'melon')
,(16,'banana')
,(18,'watermelon')
,(19,'peanuts')
,(20,'orange')
,(24,'hamburger')
,(25,'maron');

- CTE将按ID对行进行排序

-连接将读取邻接行，其中nr位于下面或上面

WITH GaplessNumbered AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS nr
          ,*
    FROM @tbl
)
SELECT prev.name,t1.name,nxt.name
FROM GaplessNumbered AS t1 
LEFT JOIN GaplessNumbered AS prev ON prev.nr=t1.nr-1
LEFT JOIN GaplessNumbered AS nxt ON nxt.nr=t1.nr+1
WHERE t1.ID=16

你甚至可能会放过WHERE。

Answer 2

应该在大多数SQL方言中工作的一个通用解决方案是：

SELECT id, name
FROM table
WHERE id BETWEEN 
    (SELECT max(id) FROM table WHERE id < 16)
    AND
    (SELECT min(id) FROM table WHERE id > 16)

Answer 3

这取决于您正在使用的数据库。在Oracle中，您可以使用铅和滞后函数：

select id, name, 
LAG(id) OVER (order by id) as prev_id,
LAG(name) OVER (order by id) as prev_name,
LEAD(id) OVER (order by id) as next_id,
LEAD(name) OVER (order by id) as next_name
from fruits
order by id;