多表查询_未参加考试的学生_学生成绩档_case when then else end

sql表

班级表class

学生表student

成绩表scores

1:查询没有参加考试(没有成绩表)的学生

SELECT s.sid,s.sname,s.sex,c.cname,sc.languages,sc.math 
FROM student as s 
LEFT JOIN class as c 
on s.cid = c.cid 
LEFT JOIN scores as sc 
on s.sid = sc.sid 
WHERE sc.languages is null and sc.math is null ;

2:查询学生的成绩档

SELECT s.sid,s.sname,s.sex,c.cname,
(CASE 
	WHEN sc.languages >= 80 THEN '优秀' 
	WHEN sc.languages >= 60 THEN '及格'
	WHEN sc.languages >= 0 THEN '不及格'
	ELSE '未参加考试'
END 
) as languages,
(CASE 
	WHEN sc.math >= 80 THEN '优秀' 
	WHEN sc.math >= 60 THEN '及格'
	WHEN sc.math >= 0 THEN '不及格'
	ELSE '未参加考试'
END 
) as math
FROM student as s 
LEFT JOIN class as c 
on s.cid = c.cid 
LEFT JOIN scores as sc 
on s.sid = sc.sid ;