590
社区成员
发帖
与我相关
我的任务
分享
select top 1 Id from Product where Id<19748
UNION
select top 1 Id from Product where Id>19748
SELECT Id
FROM ( SELECT [Id] ,
ids = ROW_NUMBER() OVER (ORDER BY Id DESC )
FROM Product
) a
WHERE Id =19748
ORDER BY Id DESC;
;WITH product(id,name)
AS
(
select 10,N'你' union all
select 11,N'我' union all
select 13,N'他' union all
select 17,N'她' union all
select 20,N'它'
),CTET
AS
(
SELECT id=0,name='',RN=0
UNION ALL
SELECT *,ROW_NUMBER()OVER(ORDER BY id) AS RN FROM product
UNION ALL
SELECT ID=0,name='',COUNT(*)+1 FROM product
)
SELECT id,t.name FROM CTET AS t WHERE EXISTS(SELECT 1 FROM CTET WHERE id=10 AND ABS(RN-t.RN)<=1)
/*
id name
0
10 你
11 我
*/
;WITH product(id,name)
AS
(
select 10,N'你' union all
select 11,N'我' union all
select 13,N'他' union all
select 17,N'她' union all
select 20,N'它'
)
SELECT
*
FROM (SELECT id,LAG(id,1,0)OVER(ORDER BY id) AS 前一行ID,LEAD(id,1,0)OVER(ORDER BY id) AS 后一行ID FROM Product ) AS t
WHERE Id=10
/*
id 前一行ID 后一行ID
10 0 11
*/
;WITH product
AS
(
SELECT 1 AS id
UNION ALL
SELECT 100
UNION ALL
SELECT 19748
UNION ALL
SELECT 20000
UNION ALL
SELECT 30000)
SELECT
*
FROM (SELECT id,LAG(id)OVER(ORDER BY id) AS 前一行,LEAD(id)OVER(ORDER BY id) AS 后一行 FROM Product ) AS t
WHERE Id=19748
/*
id 前一行 后一行
19748 100 20000
*/