select identity(int,1,1) as id ,* into #tmp from table
order by col
--先对col排序
declare @max int
select @max=max(id) from distselect * from #tmp
where id between @max/2 and @max/2+(@max+1)%2
order by col
--先对col排序
declare @max int
select @max=max(id) from distselect * from #tmp
where id between @max/2 and @max/2+(@max+1)%2
from dist a cross join dist b
group by a.c1
having (count(case when b.c1<=a.c1 then 1 else null end)>=(count(*)+1)/2)
from dist a cross join dist b
group by a.c1
having (count(case when b.c1<=a.c1 then 1 else null end)>=(count(*)+1)/2)
and (count(case when b.c1>=a.c1 then 1 else null end)>=count(*)/2+1)
还可以这样吧:
调试:
declare @a table (a int)
insert @a select 4 union select 8 union select 10 union select 3 union select 2 union select 15 union select 7 union select 1 union select 6 union select 100 union select 120 select a.a from @a a , @a b group by a.a
having (count(case when b.a<=a.a then 1 else null end)=(count(*)+1)/2)