用mysql中的in替换嵌套查询中的Using Except

Question

我正在字符串中使用查询中的exception，但mysql不支持exception。有什么方法可以重写这个查询吗。要求使用嵌套查询IN语句。所以我不能使用存在。

select distinct course_id
From course As C 
Where C.course_id IN ((Select S.course_id
From Section As S
Where S.year='2019' AND S.semester='Fall')
EXCEPT (Select S.course_id
From Section As S
Where S.year='2018' AND S.semester='Spring'));

Answer 1

您可以使用NOT IN代替EXCEPT:

select distinct course_id
From course As C 
Where C.course_id IN ((Select S.course_id
From Section As S
Where S.year='2019' AND S.semester='Fall')
and S.course_id NOT IN (Select S.course_id
From Section As S
Where S.year='2018' AND S.semester='Spring'));

Answer 2

在MYSql中，除了可以使用NOT EXISTS之外，还可以替换。这是非常标准的替换方式。

select distinct course_id
From course As C 
Where C.course_id IN ((Select S.course_id
From Section As S
Where S.year='2019' AND S.semester='Fall'
not exists (Select S.course_id
From Section As Sec
Where Sec.year='2018' AND Sec.semester='Spring' and Sec.course_id=S.course_id));

Answer 3

另一种方法(无子查询(：

SELECT c.course_id
FROM course AS c
INNER JOIN Section AS s ON s.course_id = c.course_id
GROUP BY c.course_id
HAVING GROUP_CONCAT(CONCAT(CAST(s.year AS CHAR(4)), s.semester)) LIKE '%2019Fall%' 
AND GROUP_CONCAT(CONCAT(CAST(s.year AS CHAR(4)), s.semester)) NOT LIKE '%2018Spring%'
ORDER BY c.course_id;

如果您只需要course_id字段，那么您不需要加入表course，因为字段course_id也在表部分中。

Answer 4

假设Section.course_id被定义为course.course_id的外键，那么查询中不需要表course
只在表Section中的GROUP BY course_id，并在HAVING子句中设置条件：

SELECT course_id
FROM Section
GROUP BY course_id
HAVING SUM(year='2019' AND semester='Fall') > 0
AND SUM(year='2018' AND semester='Spring') = 0