对于我来说很N的一个SQL :选出传入@ID 选出除@ID外的近四个邻近的ID

 

 

群内"水族"兄的SQL

 

set nocount on
if object_id('tb')is not null drop table tb
go
create table tb(ID int,  bb varchar(10), cc varchar(10))
insert tb select 1,    '**'  ,'hj'
insert tb select 3,    '88',''
insert tb select 4,    '**', 'dfhfgf'
insert tb select 6,    'ld8','dfg'
insert tb select 7,    ' fad',''
insert tb select 8,    ' fyae','sdfgdf'
insert tb select 9,    ' adafd','gg'
--當ID=1
select * from (select top 4  * from tb where id!=7 order by abs(7-id) )
c order by c.id asc
select top 4  * from tb where id!=7 order by abs(7-id)
/*ID          bb        
----------- ----------
3           88
4           **
6           ld8
7            fad*/
--當ID=4
select top 4 * from tb where id!=4 order by abs(4-id)
/*ID          bb        
----------- ----------
3           88
6           ld8
7            fad
1           ***/

 

达到如下目的:

 

要选出前面2个后面2个,如果前面没有才选后面的4个,如果后面没有,选前面4个