这样?select top 1 * from 表 where id<333 order by id desc union all select top 1 * from 表 where id>333
select t.* from 表 t where t.ID<333 not exists(select 1 from 表 where ID>t.ID and ID<333) union all select t.* from 表 t where t.ID>333 not exists(select 1 from 表 where ID<t.ID and ID>333)
select min(id) from table where id > '333' select max(id) from table where id < '333'
create table T(id int,cout int) insert into T select 111,1 union all select 222,2 union all select 333,3 union all select 444,4 union all select 832,5 declare @id int set @id=333 select * from (select top 1 * from T where id<@id order by id desc ) t1 union all select * from (select top 1 * from T where id>@id order by id) t2drop table T
id cout ----------- ----------- 222 2 444 4
create table T(id int,cout int) insert into T select 111,1 union all select 222,2 union all select 333,3 union all select 444,4 union all select 832,5 select max(id) from t where id < '333' union select min(id) from t where id > '333' drop table t
如果记录多的话,有两个办法:1。先对ID建立索引。2。ID有没有规律,如果有的话,可以用猜测的方法先确定一个搜索的范围再找,这样会快很多。 比如,ID的之间的差距最大是200,最小是1的话。SELECT top 403 * from table where id > 300 - 201 order by id再用上面大家的方法查询。
改了一些,看这个。如果记录多的话,可以下面两个办法一起用:1。先对ID建立索引。2。ID有没有规律,如果有的话,可以用猜测的方法先确定一个搜索的范围再找,这样会快很多。 比如,ID的之间的差距最大是200,最小是1的话。SELECT * from table where id > 300 - 201 and id < 300 + 201再用上面大家的方法查询。
ID没有这个规律,不过有用户可以过滤。 有个userid。
declare @t table (nid int) insert into @t select id from 表 where userid = 56 select max(nid) from @t where nid < 333 union all select min(nid) from @t where nid > 333 我是这么做的,先把指定用户的记录的ID放到一个表变量里,然后再到这个内存表里去查询,应该会快一点吧。
SELECT * FROM 表 WHERE ID=( SELECT MAX(ID) FROM 表 WHERE ID < 300 ) UNION ALL SELECT * FROM 表 WHERE ID=( SELECT MIN(ID) FROM 表 WHERE ID > 300 )ID主键索引,应该是最快的了
SELECT * FROM Sys_Log WHERE ID=( SELECT MAX(ID) FROM Sys_Log WHERE ID < 300 ) OR ID=( SELECT MIN(ID) FROM Sys_Log WHERE ID > 300 )不好意思,没看清题,这样就只有一句了没用使用 UNION 合并记录
SELECT * FROM 表 WHERE ID=( SELECT MAX(ID) FROM 表 WHERE ID < 333 ) OR ID=( SELECT MIN(ID) FROM 表 WHERE ID > 333 )
declare @T table (id int,cout int) insert into @T select 111,1 union all select 222,2 union all select 333,3 union all select 444,4 union all select 832,5 declare @i int set @i=333 select top 1 * from (select top 1 percent* from @t where id<@i order by id desc)a union all select top 1 * from (select top 1 percent* from @t where id>@i order by id asc)b
union all
select top 1 * from 表 where id>333
union all
select t.* from 表 t where t.ID>333 not exists(select 1 from 表 where ID<t.ID and ID>333)
select max(id) from table where id < '333'
insert into T
select 111,1 union all
select 222,2 union all
select 333,3 union all
select 444,4 union all
select 832,5
declare @id int
set @id=333
select * from
(select top 1 * from T where id<@id order by id desc ) t1
union all
select * from
(select top 1 * from T where id>@id order by id) t2drop table T
----------- -----------
222 2
444 4
insert into T
select 111,1 union all
select 222,2 union all
select 333,3 union all
select 444,4 union all
select 832,5
select max(id) from t where id < '333' union
select min(id) from t where id > '333' drop table t
比如,ID的之间的差距最大是200,最小是1的话。SELECT top 403 * from table where id > 300 - 201 order by id再用上面大家的方法查询。
比如,ID的之间的差距最大是200,最小是1的话。SELECT * from table where id > 300 - 201 and id < 300 + 201再用上面大家的方法查询。
有个userid。
insert into @t select id from 表 where userid = 56
select max(nid) from @t where nid < 333
union all
select min(nid) from @t where nid > 333
我是这么做的,先把指定用户的记录的ID放到一个表变量里,然后再到这个内存表里去查询,应该会快一点吧。
UNION ALL
SELECT * FROM 表 WHERE ID=( SELECT MIN(ID) FROM 表 WHERE ID > 300 )ID主键索引,应该是最快的了
WHERE ID=( SELECT MAX(ID) FROM Sys_Log WHERE ID < 300 )
OR
ID=( SELECT MIN(ID) FROM Sys_Log WHERE ID > 300 )不好意思,没看清题,这样就只有一句了没用使用 UNION 合并记录
WHERE ID=( SELECT MAX(ID) FROM 表 WHERE ID < 333 )
OR
ID=( SELECT MIN(ID) FROM 表 WHERE ID > 333 )
insert into @T
select 111,1 union all
select 222,2 union all
select 333,3 union all
select 444,4 union all
select 832,5 declare @i int
set @i=333
select top 1 *
from (select top 1 percent* from @t where id<@i order by id desc)a
union all
select top 1 *
from (select top 1 percent* from @t where id>@i order by id asc)b