【sql server】取当前新闻详情的上一篇和下一篇内容

通过自增的主键ID排序来取值，缺点是可能无法取到想要的值（id>当前id但时间小于当前id的新闻时间）

以ID倒序来取
--上一篇
select top 1 * from FAP_NEWS b  where b.id>1387 order by b.id asc
--结果为1388

--下一篇
select top 1 * from FAP_NEWS b where b.id<1387 order by b.id desc
--结果为1386

改进代码，使用WITH语句块，将共用查询结果放入临时表，先对目标表FAP_NEWS 进行排序，将排序后的结果放入WITH语句块，为共用部分（临时表），从临时表中通过需要查询的条件ID查询出该ID新闻的序号，通过-+序号查询该条新闻在表中的上下篇新闻

--创建WITH语句块，with语句块查询出来的内容保存在临时表中
--以时间倒序排列下一篇
WITH TUsers AS 
(select ROW_NUMBER() OVER (ORDER BY createDate desc) as ROW,b.* from FAP_NEWS b )
SELECT id,newsTitle,titleLink,content,logoImg,newsDetail,createDate,menuid,source,remark,editDate
 FROM TUsers AS tt
WHERE 
tt.ROW = ((select ROW from TUsers as T 
where T.id = 1447) + 1)

--以时间倒序拍了上一篇
WITH TUsers AS 
(select ROW_NUMBER() OVER (ORDER BY createDate desc) as ROW,b.* from FAP_NEWS b )
SELECT id,newsTitle,titleLink,content,logoImg,newsDetail,createDate,menuid,source,remark,editDate
 FROM TUsers AS tt
WHERE 
tt.ROW = ((select ROW from TUsers as T 
where T.id = 1447) - 1)

或者利用该表的排序字段

--按时间倒序排列
--上一篇
select top 1 * from FAP_NEWS b 
where  b.createDate > 排序字段的当前需要查询的值 order by b.createDate asc
--下一篇
select top 1 * from FAP_NEWS b 
where  b.createDate < 排序字段的当前需要查询的值 order by b.createDate desc

EG:

当前新闻的ID为1446，其时间为‘2021-10-26 11:06:16’，则执行

--按时间倒序排列
--上一篇
select top 1 * from FAP_NEWS b 
where  b.createDate > '2021-10-26 11:06:16' order by b.createDate asc
--下一篇
select top 1 * from FAP_NEWS b 
where  b.createDate < '2021-10-26 11:06:16' order by b.createDate desc

SQL SERVER前一行和后一行函数，仅支持2012以上的版本

LAG(提取出的表字段)OVER(ORDER BY 表字段（排序）) AS 前一行
LEAD(提取出的表字段)OVER(ORDER BY 表字段（排序）) AS 后一行