比如:现在有编号为1、2、3、4、5、6、7、8、9、10的十条记录
与1最临近的记录为:2、3、4、5
与4最临近的记录为:2、3、5、6
与10最临近的记录为:6、7、8、9我现在最笨的办法是一个一个对比,然后在union,请各位给一个最简便的方法
与1最临近的记录为:2、3、4、5
与4最临近的记录为:2、3、5、6
与10最临近的记录为:6、7、8、9我现在最笨的办法是一个一个对比,然后在union,请各位给一个最简便的方法
select top 4 * from tablename order by abs(coluname -1)
--与4最临近的记录为
select top 4 * from tablename order by abs(coluname -4)
insert into #
select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10declare @a int
set @a = 5
select top 2 id from # where id > @a
union
select id from (select top 2 id From # where id < @a order by id desc)t
drop table #
--与1最临近的记录为
select top 4 * from tablename where columnname <> 1 order by abs(columnname - 1)
--与4最临近的记录为
select top 4 * from tablename where columnname <> 4 order by abs(columnname - 4)
declare @t table(id int)
insert into @t
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10declare @id int
set @id = 4
select top 4 * from @t order by abs(id - @id)
insert into @t
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10declare @id int
set @id = 4
select top 4 * from @t where id <> @id order by abs(id - @id)
比如现在的记录是
1 2 6 8 9 10 15 20
楼主的意思是不是取出某一条相怜的上下两条记录
例:取出第9条相邻的4条记录为:
6 8 10 15
用上面游标的访求能实现吗?
修改一下
--与1最临近的记录为
select top 4 * from tablename where columnname <> 1 order by abs(columnname - 1)
--与4最临近的记录为
select top 4 * from tablename where columnname <> 4 order by abs(columnname - 4)我倒觉得这个有用,不过得判断量不是第一条,或者说两头跟中间的情况不一样
insert into @t1 select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10--与6最临近的记录为
select * into #temp1
from @t1 order by abs(id - 6)select top 4 * from #temp1
where id not in (select top 1 id from #temp1)--result
id
7
5
8
4
http://community.csdn.net/Expert/topic/5238/5238974.xml?temp=.947735
insert into @t
select 1 union all
select 2 union all
select 6 union all
select 8 union all
select 9 union all
select 10 union all
select 15 union all
select 20 union all
select 22 union all
select 23declare @id int
set @id = 10
select top 4 * from @t t where t.id <> @id
order by (select count(1) from @t where (id < t.id and id > @id) or (id > t.id and id < @id))
insert into #
select 86715
union all select 86716
union all select 86717
union all select 86718
union all select 86719
union all select 86720
union all select 86721
union all select 86722
union all select 86723
union all select 86725
union all select 86726
union all select 86727
union all select 86730
union all select 86731
union all select 86732
union all select 86733
union all select 86734
union all select 86735
union all select 86740
union all select 86743
union all select 86746
union all select 86747
union all select 86749
union all select 86750
union all select 86751
union all select 86752
union all select 86754
union all select 86756
union all select 86757
union all select 86758
union all select 86759
union all select 86760
union all select 86761
union all select 86762
union all select 86763
union all select 86764
union all select 86766--语句
select top 4 * from # t1
where id <> 86727
order by (select count(1) from # where id > t1.id and id < 86727 or id < t1.id and id > 86727 )
--结果
86726
86730
86725
86731
结果应该是:86725
86726
86727
86730
86731
--建表ta
create table ta(id int)
insert ta
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10 --创建存储过程
create proc test_p @id int
as
begin
if @id=any(select id from ta t where (select count(*) from ta where id<t.id)!>2)
begin
select top 4 * from ta where id!=@id
end
if @id=any(select id from ta t where (select count(*) from ta where id>t.id)!>2 )
begin
select top 4 * from ta where id!=@id order by id desc
end
if @id!=all(
select id from ta t where (select count(*) from ta where id<t.id)!>2
union all
select id from ta t where (select count(*) from ta where id>t.id)!>2 )
begin
select * from
(select top 2 * from ta where id<@id order by id desc)a
union all
select top 2 * from ta where id>@id order by id desc
end
end测试1:
exec test_p 3
id
-----------
1
2
4
5(所影响的行数为 4 行)
测试2:
exec test_p 1
id
-----------
2
3
4
5(所影响的行数为 4 行)测试3:
exec test_p 10
id
-----------
9
8
7
6(所影响的行数为 4 行)测试4:
exec test_p 5
id
-----------
7
6
4
3(所影响的行数为 4 行)
如果没有通过临时表实现
create proc test_p @i int
as
select *,id=identity(int,1,1) into #
from ta
以下语句把ta表换#表
id=@id不变
to coolingpipe(冷箫轻笛):
结果应该是:86725
86726
86727
86730
86731
===============================================那就是说算上自己取5条?
那
select top 5 * from # t1
order by (select count(1) from # where id > t1.id and id < 86727 or id < t1.id and id > 86727 )就可以了阿!
select * from tablename where coluname<>1 and abs(coluname -1)<=4
--与4最临近的记录为
select top 4 * from tablename where coluname<>4 and abs(coluname -4)<=2