时间戳

首页 > 解决方案 > MySQL - 选择所有不包含某些值的唯一记录

mysql - MySQL - 选择所有不包含某些值的唯一记录

问题描述

（对不起，如果问题之前已经问过或者太简单了）

从学生表中选择所有唯一名称，其中 level = 'High school' 并且没有运动（'Soccer'、'Basketball'）科目。

ID      name    level          subject

00001   John    High school Science
00002   John    High school Math
00003   John    High school *Soccer*
00004   John    High school English
00005   Andrea  High school Math
00006   Andrea  High school Science
00007   Andrea  High school English
00008   Susan   High school History
00009   Susan   High school English
00010   Susan   High school Math
00011   Michael High school Since
00012   Michael High school Math
00013   Michael High school *Basketball*
00014   Michael High school English
00015   Mary    Middle school   Math

我尝试像这样使用'EXISTS'：

SELECT ID, name, level FROM Students WHERE level = 'High school' AN NOT EXISTS(
   SELECT * FROM Students WHERE subject IN ('Soccer', 'Basketball') );

结果应该是：

Andrea  High school
Susan   High school

标签： mysqlsqlselectdistinctexists

解决方案

您需要使用以下列将子查询与外部查询相关联name：

SELECT DISTINCT s.name,  s.level
FROM students s
WHERE s.level = 'High school'
AND NOT EXISTS (
    SELECT 1 FROM students s1 WHERE s1.name = s.name AND s1.subject IN ('Soccer', 'Basketball')  
)

如果没有相关性，子查询实际上会检查所有记录中students的主题是否与'Soccer'and不同'Basketball'，这是错误的，导致外部查询不返回任何结果。

Db Fiddle上的这个演示与您的示例数据产生：

| name   | level       |
| ------ | ----------- |
| Andrea | High school |
| Susan  | High school |

推荐阅读