多列去掉两个最小值求平均

对于多列此类聚合问题,SQL比较容易.

 

需要考虑的是:

NULL的处理;

相同值的处理,

其他,没有什么难度.

 

看一下代码:

with t as
( 
select 班级,科目,成绩,DENSE_RANK() over (partition by 班级,科目 order by 成绩) as rn,
row_number() over (partition by 班级,科目 order by 成绩) as rn1 
from dbo.分数
unpivot
(成绩 for 科目 in (语文,数学,英语,政治,物理,化学,生物,体育,美术,音乐,历史,地理)) p
)
select 班级,科目,convert(decimal(11,2),AVG(t1.成绩)) as 平均,(select convert(decimal(11,2),AVG(t2.成绩)) from
t as t2 
where t2.rn >2 and
t2.班级 = t1.班级 and
t2.科目 = t1.科目
group by 班级,科目) as 平均2,
(select convert(decimal(11,2),AVG(t3.成绩)) from
t as t3 
where t3.rn1 >2 and
t3.班级 = t1.班级 and
t3.科目 = t1.科目
group by 班级,科目) as 平均1
from t as t1
group by 班级,科目
order by 班级,科目;

 

最后看一下在EXCEL的效果: