求分类统计sql，有点难度

码拜

9年 ago

需求：统计等级：任意3门课程大于80为优秀，大于有三门课60小于80为一般，有任意三门课小于60为不及格。
（可能出现同时有三门课大于80，和三门课程小于60，则取最高等级即优秀）。
表记录：

所求结果：
学生姓名 | 等级 | 课程平均分
测试数据：

create TABLE student(
	学生姓名 char(20) not null,
	课程 char(20) not null,
	score int not null)
insert into student values
("刘一", "java", 42),
("刘一", "c", 82),
("刘一", "python", 70),
("刘一", "php", 26),
("刘一", "js", 62),
("刘一", "oracle", 25),
("刘一", "mysql", 65),
("陈二", "java", 80),
("陈二", "c", 24),
("陈二", "python", 85),
("陈二", "php", 21),
("陈二", "js", 72),
("陈二", "oracle", 53),
("陈二", "mysql", 88),
("张三", "java", 23),
("张三", "c", 76),
("张三", "python", 60),
("张三", "php", 94),
("张三", "js", 71),
("张三", "oracle", 4),
("张三", "mysql", 39);

解决方案

SELECT
xx.stuname as 学生姓名,
CASE WHEN xx.maxgrade>=3 THEN “优秀”
WHEN xx.midgrade>=3 THEN “一般”
WHEN xx.mingrade>=3 THEN “不及格”
ELSE “未知” END as 成绩
FROM
(SELECT
`学生姓名` as stuname,
sum(CASE WHEN score>=80 THEN 1 ELSE 0 END) as maxgrade,
sum(CASE WHEN score<80 AND score>=60 THEN 1 ELSE 0 END) as midgrade,
sum(CASE WHEN score<60 THEN 1 ELSE 0 END) as mingrade
FROM
student
GROUP BY `学生姓名`
) as xx
GROUP BY xx.stuname

SELECT 学生姓名,IF(aa=3,”aa”,IF(bb=3,”bb”,”cc”))AS level,dd FROM (
SELECT 学生姓名,
SUM(CASE WHEN score>=80 THEN 1 ELSE 0 END)aa,
SUM(CASE WHEN score>=60 AND score<80 THEN 1 ELSE 0 END)bb,
SUM(CASE WHEN score<60 THEN 1 ELSE 0 END)cc,AVG(score) AS dd
FROM student
GROUP BY 学生姓名
)b