SQL08 EXISTS谓词
2021/9/6 2:08:42
本文主要是介绍SQL08 EXISTS谓词,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
SQL08 EXISTS谓词
- 理论篇
- EXISTS谓词定义
- 全称量化和存在量化
- 实践篇
- 查询不存在的数据
- 全称量化到双重否定
- 全称量化和谓词
- 集合的解法
- 谓词的解法
- 列的全称量词
理论篇
EXISTS谓词定义
谓词是一种特殊的函数,返回值是真值 。SQL中的真值有3种:True,False,Unknown。
谓词可以按照阶数进行划分:
- 一阶谓词:输入值为一行数据:比如 =
- 二阶谓词:输入值为行的集合:比如EXISTS
- 三阶谓词:输入值为“集合的集合”的谓词
- ……
全称量化和存在量化
- 全称量化:所有的x 都满足条件P”
- 存在量化:存在(至少一个)满足条件P 的x,也就是EXISTS谓词
我们可以用存在量化也就是EXISTS谓词去表示全称量化。
即 所有的x 都满足条件P 等价于 不存在不满足条件P 的x
实践篇
查询不存在的数据
查询没有参加会议的某人
思路如下:假设所有人每场会议都参加了,将这个集合减去现有的集合就是没有参加会议的集合。
首先生成会议和 出席者的集合
Select DISTINCT(m1.meeting,m2.persion) FROM meeting m1,meeting m2
接着使用EXISTS谓词实现减法
SELECT DISTINCT(m1.meeting,m2.person) FROM meeting m1, meeting m2 WHERE NOT EXISTS( SELECT * FROM meeting m3 WHERE m1.meeting=m3.meeting AND m2.person=m3.person )
全称量化到双重否定
请查询所有科目分数都在50分以上的同学
等价于
请查询不存在科目分数小于50分的同学
SELECT DISTINCT student_id FROM testscores t1 WHERE NOT EXISTS( SELECT * FROM testscores t2 WHERE t1.student_id=t2.student_id AND t2.score<50 )
接着我们进阶一下,请求出满足下述条件的学生
- 数学的分数在80分以上
- 语文的分数在50分以上
SELECT DISTINCT student_id FROM testscores t1 WHERE NOT EXISTS( SELECT * FROM testscores t2 WHERE t1.student_id=t2.student_id AND 1= CASE WHEN t2.subject='语文' AND t2.score<80 THEN 1 CASE WHEN t2.subject='数学' AND t2.score<50 THEN 1 ELSE 0 END )
对于400这个学生,他没有语文成绩,需要排除掉他
SELECT DISTINCT student_id FROM testscores t1 WHERE subject IN ('数学', '语文') AND NOT EXISTS( SELECT * FROM testscores t2 WHERE t1.student_id=t2.student_id AND 1= CASE WHEN t2.subject='语文' AND t2.score<80 THEN 1 CASE WHEN t2.subject='数学' AND t2.score<50 THEN 1 ELSE 0 END ) GROUP BY t1.student_id HAVING COUNT(*)=2 -- 排除400的学生
全称量化和谓词
从上述表中查询出哪些项目已经完成到了工程1
集合的解法
对于某个工程,当工程编号小于等于1时,必须是完成,当工程编号大于1时,必须是等待。等待的个数+完成的个数=总步数。
SELECT project_id FROM projects GROUP BY project_id HAVING COUNT(*)=SUM(CASE WHEN step_nbr <=1 AND status='完成' THEN 1 CASE WHEN tep_nbr >1 AND status='等待' THEN 1 ELSE 0 END)
谓词的解法
对于某个工程,当工程编号小于等于1时,必须是完成,当工程编号大于1时,必须是等待。
等价于
对于某个工程,当工程编号小于等于1时,不存在状态是等待,当工程编号大于1时,不存在状态是完成
条件可以写成
CASE WHEN step_nbr <=1 THEN status='完成' ELSE status='等待'END
完整SQL如下
SELECT project_id FROM projects p1 WHERE NOT EXISTS( SELECT * FROM projects p2 WHERE p2.status<>CASE WHEN step_nbr <=1 THEN status='完成' ELSE status='等待' END )
列的全称量词
查询全为1的行
SELECT * FROM ArrayTbl WHERE col1 = 1 AND col2 = 1 · · · AND col10 = 1
利用ALL谓词
SELECT * FROM ArrayTbl WHERE 1 = ALL (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
至少有一个9
SELECT * FROM ArrayTbl WHERE 9 = ANY (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
查询全是NULL的行
SELECT * FROM ArrayTbl WHERE COALESCE(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) IS NULL;
选出全为1的key
SELECT * FROM ArrayTbl2 a1 WHERE NOT EXISTS( SELECT * FROM ArrayTbl2 a2 WHERE a1.key=a2.key AND a2.val<>1 )
然而这样不只选出了C还出现了A。
原因在于SQL是三值逻辑,而EXISTS是二值逻辑。对于EXISTS而言,没有返回值就是FALSE,返回值就是TRUE。
所以A也会返回。
正确的写法应该排除全为NULL的情况
SELECT * FROM ArrayTbl2 a1 WHERE NOT EXISTS( SELECT * FROM ArrayTbl2 a2 WHERE a1.key=a2.key AND (a2.val<>1 OR a2.val IS NULL) )
或者使用集合的思想
极值函数解决
SELECT key FROM ArrayTbl2 GROUP BY key HAVING MAX(val) = 1 AND MIN(val) = 1;
SELECT key FROM ArrayTbl2 GROUP BY key HAVING COUNT(*)=SUM(CASE WEHN val=1 THEN 1 ELSE 0 END)
这篇关于SQL08 EXISTS谓词的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-29RocketMQ底层原理资料详解:新手入门教程
- 2024-11-29RocketMQ源码资料解析与入门教程
- 2024-11-29[开源]6.1K star!这款电视直播源神器真的太赞啦!
- 2024-11-29HTTP压缩入门教程:轻松提升网页加载速度
- 2024-11-29JWT开发入门指南
- 2024-11-28知识管理革命:文档软件的新玩法了解一下!
- 2024-11-28低代码应用课程:新手入门全攻略
- 2024-11-28哪些办公软件适合团队协作,且能够清晰记录每个阶段的工作进展?
- 2024-11-28全栈低代码开发课程:零基础入门到初级实战
- 2024-11-28拖动排序课程:轻松掌握课程拖动排序功能