gpt4 book ai didi

sql - 基于当前的下一个/上一个记录

转载 作者:行者123 更新时间:2023-12-04 14:13:12 24 4
gpt4 key购买 nike

我有一个未按任何列排序的表。如果我只知道当前的 ID,有什么方法可以选择下一个/上一个记录? (我用的是mssql)

Id     Label     Date
---------------------
1 label1 2011-01-10
7 label2 2011-01-15 -- how to get previous?
5 label3 2011-01-12 -- I know id of this record
10 label10 2011-01-25 -- how to get next?
12 label8 2011-01-13
2 label5 2011-01-29

提前致谢!

最佳答案

试试这个:

VALUES (1, 'label1', '2011-01-10'), (7, 'label2', '2011-01-15'),
(5, 'label3', '2011-01-12'), (10, 'label10', '2011-01-25'),
(12, 'label8', '2011-01-13'), (2, 'label5', '2011-01-29')

select * from table007;

Declare @inptID int=12;

;WITH CTE
as
(
select *, ROW_NUMBER() over (order by (select 0)) as rn
from table007
)

select *
from CTE
where rn in( select rn-1 from CTE where id = @inptID)
union all
select * from CTE where rn in(select rn + 1 from CTE where id = @inptID);

SQL fiddle 演示

DEMO

关于sql - 基于当前的下一个/上一个记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12620996/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com