mysql - SQL - 如何选择具有特定条件的行？

Question

我是 SQL 领域的新手，我在 Windows 7 中使用 MariaDB 10.1。

这是我创建的测试代码。

CREATE TABLE sample (
    studentName VARCHAR(32),
    subjectName VARCHAR(16),
    subjectRegistration BOOLEAN
);

INSERT
INTO    sample VALUES
    ("Tom", "Math", TRUE),
    ("Tom", "English", TRUE),
    ("Tom", "Science", TRUE),
    ("Jane", "English", TRUE),
    ("Jane", "Math", TRUE),
    ("Jane", "Science", TRUE),
    ("Peter", "Math", TRUE),
    ("Susan", "Math", TRUE),
    ("Susan", "Science", TRUE),
    ("Clark", "Math", TRUE),
    ("Clark", "English", TRUE),
    ("Clark", "Science", TRUE);

我想选择只选修数学的学生。(在本例中，是彼得。)

SELECT * FROM sample WHERE subjectName = 'Math' AND subjectRegistration = TRUE;

此查询不仅选择了 Peter，还选择了所有注册其他受试者的人。

我应该使用什么查询？

谢谢你的提前。

Answer 1

您想要做的是包括那些只学过数学的学生。在 sql 中如何执行此操作有多种变体。总体思路是找出谁选修了数学并且只选修了一门类(class)。这些只是如何执行此操作的几个示例，但每个示例都将完成您的任务。

您可以在此 Sql Fiddle 查看每个示例.

示例 1:

SELECT * 
  FROM sample 
 WHERE subjectName = 'Math'
   AND subjectRegistration = TRUE  
   AND studentName IN (SELECT studentName 
                         FROM sample 
                        WHERE subjectRegistration = TRUE
                       GROUP BY studentName 
                       HAVING count(*) = 1)

示例 2:

SELECT *
  FROM sample s
       JOIN (SELECT studentName 
               FROM sample 
              WHERE subjectRegistration = TRUE
             GROUP BY studentName
             HAVING count(*) = 1) s2 ON (s.studentName = s2.studentName)
 WHERE s.subjectName = 'Math'
   AND subjectRegistration = TRUE