传进一个ID就是搜三行记录本行,前一行,后一行
我自己写的是
(传进去值当是5)select top 3 * from table where id<(select top 1 id from table where id>5 order by id desc) order by id desc但是当id值是最后一行时就会出问题了改成select top 3 * from table where id<=(select top 1 id from table where id>=5 order by id desc) order by id desc这句冒似可以但是不是我想要的结果,我想要的是当传入的值是第一行时,那么就只搜出1,2两行,传入值为最后一行时搜出倒数1,2行大家有好的sql不?存储过程也行啊
我自己写的是
(传进去值当是5)select top 3 * from table where id<(select top 1 id from table where id>5 order by id desc) order by id desc但是当id值是最后一行时就会出问题了改成select top 3 * from table where id<=(select top 1 id from table where id>=5 order by id desc) order by id desc这句冒似可以但是不是我想要的结果,我想要的是当传入的值是第一行时,那么就只搜出1,2两行,传入值为最后一行时搜出倒数1,2行大家有好的sql不?存储过程也行啊
Select @ID = 5
Select * From (Select TOP 1 * From TEST Where ID < @ID Order By ID Desc) A
Union All
Select * From TEST Where ID = @ID
Union All
Select * From (Select TOP 1 * From TEST Where ID > @ID Order By ID ) B
select * from table where id in (pid-1,pid,pid+1)
Select @ID = 5
Select * From (Select TOP 1 * From TEST Where ID < @ID Order By ID Desc) A
Union All
Select * From TEST Where ID = @ID
Union All
Select * From (Select TOP 1 * From TEST Where ID > @ID Order By ID ) B这样的还是做了三次的查询.....
Declare @ID Int
Select @ID = 5
Select * From (Select TOP 1 * From TEST Where ID < @ID Order By ID Desc) A
Union All
Select * From TEST Where ID = @ID
Union All
Select * From (Select TOP 1 * From TEST Where ID > @ID Order By ID ) B
。
Select @ID = 5
Select * From (Select TOP 1 * From TEST Where ID < @ID Order By ID Desc) A
Union All
Select * From TEST Where ID = @ID
Union All
Select * From (Select TOP 1 * From TEST Where ID > @ID Order By ID ) B我的表好像有100w行不到的记录哦,三次查询跟我现在已经实现的性能差不多
Select * From TEST Where ID In((Select Min(ID) From TEST Where ID > 5), 5, (Select Max(ID) From TEST Where ID < 5))
declare @rownumber
select * from 你的表 into @result
select @rownumber=rownumber where id=@id
if @rownumber is not null
select 你要的列 from @result where rownumber>=rownumber+1 and rownumber<=rownumber-1这样就行了
别的数据类型恐怕难SELECT * FROM WHERE ID>(@ID-2) AND ID <(ID+2)
===================================declare @result table(rownumber int identity(1,1),后面的与你的表结构一样)
declare @rownumber
select * from 你的表 into @result
select @rownumber=rownumber where id=@id
if @rownumber is not null
select 你要的列 from @result where rownumber>=rownumber+1 and rownumber<=rownumber-1这样就行了
==============
表有100W左右哦
这样来ID似乎必须是数字拉
别的数据类型恐怕难SELECT * FROM WHERE ID>(@ID-2) AND ID <(ID+2)
-------------
ID不一定連續
别的数据类型恐怕难SELECT * FROM WHERE ID>(@ID-2) AND ID <(ID+2)id是int,但是id 不是一直+1
把条件中的ID换成记数值
这样其实还是复合查询了 效率也还可以
? try
Select * From TEST Where ID In((Select Min(ID) From TEST Where ID > 5), 5, (Select Max(ID) From TEST Where ID < 5))--------------------
鱼,不亏是SQL版的高手
求两次查询搞定代码
------------
查詢次數多少和效率之間應該沒有絕對的關係。
top是用的什么算法?知道不?