mysql去最大最小值求平均值

最新推荐文章于 2022-07-11 16:44:09 发布

原创最新推荐文章于 2022-07-11 16:44:09 发布 · 9.3k 阅读

1 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

26 篇文章

订阅专栏

本文介绍了一个用于学生分数统计与分析的SQL脚本。该脚本首先创建了一个包含学生学号、姓名、课程编号、课程名称及分数的成绩表，并插入了一些示例数据。接着，通过SQL查询计算了每位学生的平均分数（排除最高最低分），并按分数段统计了各分数范围内的人数。

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;