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

对于多列此类聚合问题,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.成绩)) fromt as t2 where t2.rn >2 andt2.班级 = t1.班级 andt2.科目 = t1.科目group by 班级,科目) as 平均2,(select convert(decimal(11,2),AVG(t3.成绩)) fromt as t3 where t3.rn1 >2 andt3.班级 = t1.班级 andt3.科目 = t1.科目group by 班级,科目) as 平均1from t as t1group by 班级,科目order by 班级,科目;

 

最后看一下在EXCEL的效果: