mysql - 检索满足一个条件但不满足另一个条件的记录

Question

我有一个名为 tuition
的表我想检索所有学习 111 和不学习 333
的学生这是我能想到的:

http://www.sqlfiddle.com/#!2/f6411/3

stud_id | subject_id
--------------------
    1       111
    1       222
    2       222
    2       333
    3       111
    3       222
    3       333
    4       111
    4       222

输出:

stud_id
-------
   1
   4 

Answer 1

三种选择:

1. 使用 HAVING 子句和 WHERE 子句:

   SELECT *
   FROM tution 
   WHERE subject_id = 111 OR subject_id=333
   GROUP BY stud_id
   HAVING COUNT(DISTINCT subject_id)=1 AND subject_id<>333
   

   结果为 SQL Fiddle .

2. 使用不带 WHERE 子句的 HAVING 子句:

   SELECT *
   FROM tution 
   GROUP BY stud_id
   HAVING COUNT(DISTINCT subject_id)=2 AND subject_id =111 AND subject_id<>333
   

   结果为 SQL Fiddle .

3. 使用 IN:

   SELECT *
   FROM tution
   WHERE subject_id=111
   AND stud_id NOT IN (SELECT stud_id FROM tution WHERE subject_id= 333)
   

   结果为 SQL Fiddle .

结果:

STUD_ID  SUBJECT_ID
1        111
4        111