【MySQL】查询学生成绩，并作详细分级:

【MySQL】查询学生成绩，并作详细分级:

case ... end 语句做嵌套

 

#创建学生成绩

#创建学生成绩
create database test;
use test;
create table course
(
	Student_id char(12) not null comment "学号",
	Course_id char(4) not null comment "课程号",
	score int default 0 comment "成绩"
);

insert into course values
	("201810101101","1001",65),
	("201810101102","1001",76),
	("201810101103","1001",87),
	("201810101104","1001",69),
	("201810101105","1001",96),
	("201810101106","1001",82),
	("201810101107","1001",72),
	("201810101108","1001",64);

 

select
	Student_id as 学号,
	Course_id as 课程编号,
	(case when score >= 90  then "优秀" 
	else
			(case when score >= 80 then "良好"
			else
					(case when score >=70 then "中等" 
					else
							(case when score>=60 then "及格" else "不及格" end)
					end)
			end)
	end) as 成绩情况
from course;