表:
---------------------------------
id
1
2
3
4
5
6
---------------------------------
结果集合:
---------------------------------
1
6
2
5
3
4
---------------------------------
id
1
2
3
4
5
6
---------------------------------
结果集合:
---------------------------------
1
6
2
5
3
4
insert tb
select 1
union select 2
union select 3
union select 4
union select 5
union select 6select n=identity(int,1,2),* into # from tb
select n=identity(int,2,2),* into #1 from tb order by id desc
select top 6 id from (
select * from #
union
select * from #1) tdrop table #,#1
drop table tb/* 结果
id
-----------
1
2
3
4
5
6(6 row(s) affected)
*/
insert tb
select 1
union select 2
union select 3
union select 4
union select 5
union select 6select id
from
(
select
a=(select count(*) from tb where id<=t1.id ),id
from tb t1
where id in(select top 50 percent id from tb order by id)
union all
select
a=(select count(*) from tb where id>=t2.id),id
from tb t2
where id not in(select top 50 percent id from tb order by id)
) A
order by a,id
/*
id
-----------
1
6
2
5
3
4
*/drop table tb
insert @a 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
select id from(
select top 100 percent id,x=(select count(1) from @a where id<=a.id) from @a a order by x
union all
select top 100 percent id ,x=(select count(1) from @a where id>=a.id) from @a a order by x desc
)aa where x<=ceiling((select count(1) from @a)/2.0) order by x,id
/*
id
-----------
1
7
2
6
3
5
4
4(所影响的行数为 8 行)
*/