sql server中中值的计算方法

1.创建课程表course

字段有coursename nvarchar(50) 课程名,score float 分数

2.插入如下数据

insert into course values('语文',82)
insert into course values('语文',85)
insert into course values('语文',75)
insert into course values('语文',64)
insert into course values('语文',90)
insert into course values('语文',100)
insert into course values('数学',50)
insert into course values('数学',80)
insert into course values('数学',45)
insert into course values('数学',100)
insert into course values('数学',82)

3.中值计算SQL如下

WITH temp AS 
( 
SELECT coursename,score, 
ROW_NUMBER() OVER(PARTITION BY coursename ORDER BY score) AS RowNum, 
COUNT(*) OVER(PARTITION BY coursename) AS Cnt 
FROM course 
)
select coursename,avg(score) from ( 
SELECT coursename,score,RowNum,Cnt 
FROM temp
WHERE RowNum IN((Cnt + 1) / 2,(Cnt + 2) / 2)  
)a 
group by coursename

4.结果如下

coursename,score

数学                80

语文                83.5