SQL Server 关于求中位数的一个解决方案

sql server中并没有内置求中位数的聚合函数，所以我们需要自己写sql语句

样表如下：

create table My_test 			--学生成绩表
(
id int primary key identity,
stu_No int,						--学号
stu_Name nvarchar(10),			--姓名
times int,						--第几次考试
stu_Score int					--成绩
)

现在要求计算每名学生成绩的中位数

利用ROW_NUMBER() OVER函数分组并编号
count(*)统计每个学生总考试次数

select * into #temp 
from (select *,
row_number() over (partition by stu_No,stu_Name order by stu_Score desc) as group_idex,
count(*) over (partition by stu_No) as total_times
from My_test) as s

完成编号之后求中位数就很轻松了，根据group_idex与total_times来确定

select stu_No,stu_Name,stu_Score as med_Score from #temp where total_times%2<>0 and group_idex=(total_times+1)/2 
--考试次数为奇数的中位数
union
select t.stu_No,t.stu_Name,(t.stu_Score+s.stu_Score)/2.0 as med_Score from 
(select * from #temp where total_times%2=0 and group_idex=total_times/2) as t,
(select * from #temp where total_times%2=0 and group_idex=total_times/2+1) as s
where t.stu_No=s.stu_No and t.stu_Name=s.stu_Name
--考试次数为偶数的中位数

drop table #test

如有错误，欢迎各位大佬指正