上(包含80)且语文分数在 50 分以上(包含)的学生”
结果应该是学号分别为 20190607001、20190608003 的学生。像这样的需求,我们在实际业务中应该会经常遇到,但是乍一看可能会觉得不太像是全称量化的条件。如果改成下面这样的说法,可能我们一下子就能明白它是全称量化的命题了。
"某个学生的所有行数据中,如果科目是数学,则分数在 80 分以上;如果科目是语文,则分数在 50 分以上。"
我们再转换成它双重否定:某个学生的所有行数据中,如果科目是数学,则分数不低于 80;如果科目是语文,则分数不低于 50 ;我们可以按照如下顺序写出我们想要的 SQL
-- 1、CASE 表达式,肯定
CASE WHEN subject = '数学' AND score >= 80 THEN 1
WHEN subject = '语文' AND score >= 50 THEN 1
ELSE 0
END;
-- 2、CASE 表达式,单重否定(加上 NOT EXISTS才算双重)
CASE WHEN subject = '数学' AND score < 80 THEN 1
WHEN subject = '语文' AND score < 50 THEN 1
ELSE 0
END;
-- 3、结果包含了 20190610011 的 SQL
SELECT DISTINCT sno
FROM tbl_student_score tss1
WHERE subject IN ('数学', '语文')
AND NOT EXISTS
(
SELECT *FROM tbl_student_score tss2
WHERE tss2.sno = tss1.sno
AND 1 = CASE WHEN subject = '数学' AND score < 80 THEN 1
WHEN subject = '语文' AND score < 50 THEN 1
ELSE 0
END
);
-- 4、20190610011 没有语文成绩,剔除掉
SELECT sno
FROM tbl_student_score tss1
WHERE subject IN ('数学', '语文')
AND NOT EXISTS
(
SELECT * FROM tbl_student_score tss2
WHERE tss2.sno = tss1.sno
AND 1 = CASE WHEN subject = '数学' AND score < 80 THEN 1
WHEN subject = '语文' AND score < 50 THEN 1
ELSE 0
END
)
GROUP BY sno
HAVING COUNT(*) = 2; -- 必须两门科目都有分数
关于 EXISTS 的案例有很多,这里就不再举例了,有兴趣的小伙伴可以看看:SQL 中的 EXISTS 到底做了什么?
如果大家想掌握 EXISTS,希望大家多看看 EXISTS 的案例,看多了你就会发现其中的通性:哪些场景适合用 EXISTS。
总结
1、SQL 中的谓词分两种:一阶谓词和二阶谓词(EXISTS),区别主要在于接收的参数不同,一阶谓词接收的是 行,而二阶谓词接收的是 行的集合;
2、SQL 中没有与全称量词相当的谓词,可以使用 NOT EXISTS 代替;
3、EXISTS 之所以难用(不是不好用,而是不会用),主要是全称量词的命题转换(肯定 ? 双重否定)比较难(楼主也懵!)。实际工作中往往会舍弃 EXISTS,寻找它的替代方式,可能是 SQL 的替代,也可能是业务方面的转换,所以说,EXISTS 掌握不了没关系,当然,能掌握那是最好了;
参考
《SQL基础教程》
《SQL进阶教程》