SQL进阶：not exists谓词

目录

1、查询所有科目成绩都在50分以上的学生学号

2、查询数学分数在80分及以上的，语文分数在50分及以上的 学生ID

3、从表中选出val全是1的key

---

not exists：习惯“肯定<==>双重否定”之间的转换

1、查询所有科目成绩都在50分以上的学生学号

CREATE TABLE `test_scores` (
	`student_id` INT(10) NULL DEFAULT NULL COMMENT '学生ID',
	`subject` VARCHAR(50) NULL DEFAULT NULL COMMENT '课程' COLLATE 'utf8mb4_0900_ai_ci',
	`score` INT(10) NULL DEFAULT NULL COMMENT '成绩'
)
COMMENT='学生考试成绩表（肯定与双重否定之间的转换）'
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;

录入数据：

所有成绩都是50分以上，双重否定就是：没有一科成绩是在50分以下的：

SELECT DISTINCT student_id FROM test_scores a
WHERE 
NOT EXISTS (SELECT 1 
            FROM test_scores b 
            WHERE a.student_id = b.student_id 
            AND b.score < 50)

执行结果：

2、查询数学分数在80分及以上的，语文分数在50分及以上的 学生ID

双重否定就是：不存在数学分数在80分以下的或语文分数在50分以下的。

SELECT DISTINCT student_id FROM test_scores a 
WHERE SUBJECT IN ('数学','语文')
AND  NOT EXISTS 
(SELECT 1 FROM test_scores b WHERE a.student_id = b.student_id 
AND 1 = case when SUBJECT = '数学' AND score < 80 then 1
			 when SUBJECT = '语文' AND score < 50 then 1 ELSE 0 END 
)

3、从表中选出val全是1的key

双重否定就是，不存在val不等于1或val为null的key

SELECT DISTINCT a.key 
FROM array_tbl2 a 
WHERE NOT EXISTS 
(SELECT 1 
FROM array_tbl2 b 
WHERE a.`key` = b.`key` 
AND a.i = b.i 
AND (b.val <> 1 OR b.val IS NULL) )

使用having子句实现：

SELECT a.key 
FROM array_tbl2 a 
GROUP BY a.key 
HAVING SUM(case when a.val = 1 then 1 ELSE 0 END ) = COUNT(*)