if object_id('tempdb.dbo.#T1') is not null drop table #T1 go create table #T1 ([MediaID] int) insert into #T1 select 12 union all select 23 union all select 34 union all select 45 union all select 56 union all select 78 union all select 87 union all select 97 ;with tt as( select [cnt]=row_number()over(order by [MediaID]), [MediaID] from #T1 ) select top 4 * from tt where cnt!=(select top 1 cnt from tt where [MediaID]=56) order by abs((select top 1 cnt from tt where [MediaID]=56)-cnt) /* cnt MediaID -------------------- ----------- 6 78 4 45 3 34 7 87 */
if object_id('tempdb.dbo.#T1') is not null drop table #T1 go create table #T1 ([MediaID] int) insert into #T1 select 12 union all select 23 union all select 34 union all select 45 union all select 56 union all select 78 union all select 87 union all select 97 select top 100 percent * from ( select top 2 * from #T1 where [MediaID]<56 order by [MediaID] desc )a union all select top 100 percent * from ( select top 2 * from #T1 where [MediaID]>56 order by [MediaID] desc )b /* MediaID ----------- 45 34 97 87(4 row(s) affected) */
if object_id('tempdb.dbo.#T1') is not null drop table #T1 go create table #T1 ([MediaID] int) insert into #T1 select 12 union all select 23 union all select 34 union all select 45 union all select 56 union all select 78 union all select 87 union all select 97 select top 4 * from #T1 where [MediaID]<>56 order by abs([MediaID]-56)
/* MediaID ----------- 45 78 34 87
最终解决方法 查2次,然后把 2次 的结果 union all 起来谢谢大家
top 2排序加union all
create table t1 ( id int ) insert into t1 select 12 union all select 23 union all select 34 union all select 45 union all select 56 union all select 78 union all select 87 union all select 97 select * from t1;with aaa as ( select ROW_NUMBER() over(order by id) as row,* from t1 ) select * from aaa where ABS(row-(select row from aaa where id=56))<=2
go
create table #T1 ([MediaID] int)
insert into #T1
select 12 union all
select 23 union all
select 34 union all
select 45 union all
select 56 union all
select 78 union all
select 87 union all
select 97
;with tt
as(
select [cnt]=row_number()over(order by [MediaID]),
[MediaID]
from #T1
)
select top 4 * from tt
where cnt!=(select top 1 cnt from tt where [MediaID]=56)
order by abs((select top 1 cnt from tt where [MediaID]=56)-cnt)
/*
cnt MediaID
-------------------- -----------
6 78
4 45
3 34
7 87
*/
go
create table #T1 ([MediaID] int)
insert into #T1
select 12 union all
select 23 union all
select 34 union all
select 45 union all
select 56 union all
select 78 union all
select 87 union all
select 97
select top 100 percent * from
(
select top 2 * from #T1 where [MediaID]<56 order by [MediaID] desc
)a
union all
select top 100 percent * from
(
select top 2 * from #T1 where [MediaID]>56 order by [MediaID] desc
)b
/*
MediaID
-----------
45
34
97
87(4 row(s) affected)
*/
go
create table #T1 ([MediaID] int)
insert into #T1
select 12 union all
select 23 union all
select 34 union all
select 45 union all
select 56 union all
select 78 union all
select 87 union all
select 97
select top 4 * from #T1 where [MediaID]<>56
order by abs([MediaID]-56)
/*
MediaID
-----------
45
78
34
87
create table t1
(
id int
)
insert into t1
select 12 union all
select 23 union all
select 34 union all
select 45 union all
select 56 union all
select 78 union all
select 87 union all
select 97
select * from t1;with aaa as
(
select ROW_NUMBER() over(order by id) as row,* from t1
)
select * from aaa where ABS(row-(select row from aaa where id=56))<=2