有三个表
学生表 S
SID SNAME
教师课表 T
TID TNAME TCL
成绩表 SC
SID TCL SCR
各字段的含义:
SID 学生id, SNAME 学生姓名
TID 老师ID, TNAME 老师姓名, TCL 课程名(所授课程)
SID 学生id, TCL 课程名, SCR 成绩
现在要求写SQL查询
1、选修了A、B课程,并且A课程的成绩大于B成绩的学生姓名?
2、没有选修‘li’老师的课程的学生,要求不能用in,exists 等词?
参考答案如下:(仅为参考~)
(1)
SELECT M.SNAME FROM SC AS AA INNER JOIN
(
SELECT
A.SID AS SID,
A.SNAME AS SNAME,
CASE
WHEN AVG(SCR) = MAX(SCR) THEN -1
ELSE MAX(SCR)
END
AS MSCR
FROM S AS A INNER JOIN SC AS B ON A.SID = B.SID
WHERE B.TCL = 'A' OR B.TCL = 'B' GROUP BY A.SID HAVING COUNT(*)=2
) AS M ON AA.SID = M.SID
WHERE AA.TCL = 'A' AND AA.SCR = M.MSCR;
/**
题目:(1) 的解释:
第一步,找出 “即选择了A课程又选择了B课程的学生”
那么 “结果”就会出现“3种”类型的学生。
即: 第1种:只选择了 A 课程
第2种:只选择了 B 课程
第3种:A, B 都选择了的
那么如果我对这个查询结果,按照SID进行分组(group by),
那么“每组”的数据条目有可能是1 也有可能是2
即:选修了1门课程的学生是1 ,2门的则是2
然后只显示2条数目的组(即:HAVING COUNT(*)=2)
并返回 SID 和 SNAME 以及此组中的 “最高成绩 MAX(SCR)” (如果两门成绩相同则返回 -1,即为忽略)
那么我们怎么知道这个返回的“最高成绩“是A组的还是B组的呢?
我们只需要判断一下外层的数据是否是A 如果是A,则继续
AA.SCR = M.MSCR
*/
(2)
SELECT AA.SNAME FROM S AS AA
WHERE
(
SELECT COUNT(*) FROM T AS A INNER JOIN SC AS B ON A.TCL = B.TCL
WHERE
B.SID = AA.SID AND
A.TNAME like 'li'
GROUP BY B.SID
) is NULL;
/**
题目:(2) 的解释:
从外层SQL语句中,拿出一个SID,传入至内层SQL,并在内层SQL语句中
拿所有内层的SID 与 这个传入进来的SID 进行比较。
如果相等,并且 选修过 li 老师的课程。怎会返回一个数值 即:COUNT(*)
否则返回 null
*/