22,209
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE (rowNum INT,[name] NVARCHAR(8),gender NCHAR(1))
INSERT INTO @t VALUES (150,N'张三',N'男')
INSERT INTO @t VALUES (100,N'王二',N'男')
INSERT INTO @t VALUES (200,N'李四',N'女')
;WITH cte as (
SELECT ROW_NUMBER() OVER (ORDER BY rowNum DESC) AS rid ,* FROM @t
)
SELECT TOP 1 rowNum,[name],gender
FROM cte WHERE rowNum<(SELECT TOP 1 rowNum FROM cte b WHERE b.name=N'李四')
ORDER BY rowNum DESC
/*
rowNum name gender
150 张三 男
*/
if object_id('tempdb..#Tmp_Data') is not null
drop table #Tmp_Data
CREATE TABLE #Tmp_Data (
Row_No int ,
Name nvarchar(10) ,
Sex nvarchar(10))
INSERT INTO #Tmp_Data
Select 150,N'张三',N'男' union
Select 100,N'王二',N'男' union
Select 200,N'李四',N'女'
DECLARE @currRow_No int
SET @currRow_No=200
Select *
From #Tmp_Data
Where Row_No=(Select Max(Row_No)From #Tmp_Data Where Row_No<@currRow_No)
Row_No Name Sex
----------- ---------- ----------
150 张三 男
(1 row(s) affected)