日期:2014-05-18  浏览次数:20521 次

如何取中间的三条记录?
有一个表,其中一个字段my_data为Float型,如何用my_data排序以后如何取正中间的三条记录?谢谢!

------解决方案--------------------
SQL code

--6条记录 或是 4条记录 测试没有问题
declare @t table (my_data numeric(2,1))
insert into @t
select 1.1 union all
select 1.2 union all
select 1.5 union all
select 1.6 union all
select 1.7 union all
select 1.8

;with maco as
(
select row_number () over (order by my_data) as num,* from @t
)

select my_data from maco 
where num between (select ceiling(count(1)/2.0)-1 from maco)
and  (select ceiling(count(1)/2.0)+1 from maco)
/*
my_data
---------------------------------------
1.2
1.5
1.6
*/

------解决方案--------------------
SQL code

select *
from(
    select *,px=row_number() over (order by my_data)
    from tb
)t
where px between (case when px%2=0 then px/2-1 else (px+1)/2-1 end) 
         and (case when px%2=0 then px/2+1 else (px+1)/2+1 end)