id A
1 1
2 4
3 2
4 2
5 3
以上数据以A字段排序,即 order by A,这时数据
id A
1 1
3 2
4 2
5 3
2 4
当前位置是id=4问:如何获取排序后的上一行?
1 1
2 4
3 2
4 2
5 3
以上数据以A字段排序,即 order by A,这时数据
id A
1 1
3 2
4 2
5 3
2 4
当前位置是id=4问:如何获取排序后的上一行?
调试欢乐多
select top 1 * from table where A<=(select A from table where id=4) and id<4 order by A desc
select top 1 * from table where A>=(select A from table where id=4) and id>4 order by A
Select * From (Select *,ROW_NUMBER() OVER(ORDER BY a) as RowNum From tb) testtb Where RowNum = 4
insert into @tb
select 1,1 union all
select 2,4 union all
select 3,2 union all
select 4,2 union all
select 5,3
select * from @tb order by [A]select top 1 * from @tb
where [A]=(select [A] from @tb where [id]=4)
and [id]<>4
order by [A] desc
办法是有,但不效率,将所有数据加载到DataTable,然后再去判断数据
INSERT #tmp(ID,A) SELECT ID,A FROM test ORDER BY A
SELECT * FROM #tmp
--上一条记录(2 3 2)
SELECT top 1 * FROM #tmp WHERE aid < (SELECT aid FROM #tmp WHERE ID=4) ORDER BY aid DESC
--下一条记录(4 5 3)
SELECT top 1 * FROM #tmp WHERE aid > (SELECT aid FROM #tmp WHERE ID=4) ORDER BY aid
DROP TABLE #tmp
不使用游标,使用一个自增的排序aid来排序!