在网上看解题方法思路如下:
方法1:
SELECT
st.*
FROM
student st
LEFT JOIN
(
-- 子表,用来把score表里面每个学生的‘01’和‘02’课程数据拿出来
SELECT
s.s_id,
max( CASE WHEN c_id = '01' THEN s_score ELSE NULL END ) s01,
max( CASE WHEN c_id = '02' THEN s_score ELSE NULL END ) s02
FROM
score s
GROUP BY
s_id
) t ON t.s_id = st.s_id
WHERE
t.s01 >= 0 -- 查询学过‘01’课程的学生
AND t.s02 IS NULL -- 查询没有学过‘02’课程的学生
对于初学者来讲代码稍微有点长,有点难理解;
作为一个刚迈出小白门槛的我,用自己的思路写了如下方法
方法2:
SELECT DISTINCT a.*
from student a
where a.s_id in (select sc.s_id from score sc where sc.c_id = 01 )
and a.s_id not in (select s1.s_id from score s1 where s1.c_id = 02)
用两个简单的子查询简单诠释;更贴切,思路也一目了然。