mysql去最大最小值求平均值

CREATE TABLE `test_student` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID',
 `s_no` VARCHAR(32) NOT NULL  DEFAULT ''  COMMENT '学号',
 `s_name` VARCHAR(32) NOT NULL  DEFAULT ''  COMMENT '姓名',
 `c_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '课程编号',
 `c_name` VARCHAR(32) NOT NULL  COMMENT '课程名称',
 `score` tinyint unsigned not null  DEFAULT 0 COMMENT '分数',
 PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='学生成绩表';

insert into test_student (s_no,s_name,c_no,c_name,score)
values ('2005001', '张三', '001','数学',60),
('2005002', '李四', '001','数学',89),
('2005001', '张三', '001','数学',69),
('2005001', '张三', '001','数学',80),
('2005003', '李丽', '001','数学',69),
('2005004', '王强', '001','数学',69);

select s_no, case when num > 2 then
(total_score - max_score - min_score)/(num-2)
else
total_score/num
end
as avg_score
from (select s_no,sum(score) total_score,max(score) max_score, min(score) min_score,
count(1) num from test_student group by s_no) a;

---按课程取每个分数段的人数----
select s_no,s_name,
sum(if(score between 85 and 100, 1, 0)) as '85-100',
sum(if(score>=70 and score<85, 1, 0)) as '70-85',
sum(if(score>=60 and score<70, 1, 0)) as '60-70',
sum(if(score<60, 1, 0)) as '<60'
from test_student
group by s_no,s_name;