SQL08 EXISTS谓词

2024-02-13 14:39:10

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)

码农公寓