求中位数

当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数

 

CREATE TABLE class_grade (
grade varchar(32) NOT NULL,
number int NOT NULL
);

INSERT INTO class_grade VALUES
('A',1),
('C',1),
('D',1),
('B',1),
('E',1);

/*
select grade
from
(
    select grade
         , number
         , (
               select sum(number) + 1
               from class_grade x
               where x.grade < class_grade.grade
           ) as rowid_start
         , (
               select sum(number) from class_grade x where x.grade <= class_grade.grade
           ) as rowid_end
    from class_grade
) a
, (
      select sum(number)*1.00 / 2     as start_index
           , sum(number)*1.00 / 2 + 1 as end_index
      from class_grade
  ) b
where a.rowid_start between b.start_index and b.end_index
	  or
	  a.rowid_end between b.start_index and b.end_index
	  or
	  b.start_index between a.rowid_start  and a.rowid_end 
	  or
	  b.end_index between a.rowid_start  and a.rowid_end 
order by grade;
*/

select y.grade from (
select 
grade
,sum(number) over(order by grade) as sum_asc
,sum(number) over(order by grade desc ) as  sum_desc
from 
dbo.class_grade) y
,(select sum(number)*1.00/2 as num from dbo.class_grade) x
where y.sum_asc>=x.num and y.sum_desc>=x.num
order by grade

drop table class_grade;