求中位数

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;