mysql，同等版本除外

zpgglvta 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(268)

我正在为以下查询寻找mysql等价物：

(select course_id
from section
where semester = 'Fall' and year= 2009)
except
(select course_id
from section
where semester = 'Spring' and year= 2010);

其中截面表为：

+-----------+--------+----------+------+----------+-------------+--------------+
| course_id | sec_id | semester | year | building | room_number | time_slot_id |
+-----------+--------+----------+------+----------+-------------+--------------+
| BIO-101   | 1      | Summer   | 2009 | Painter  | 514         | B            |
| BIO-301   | 1      | Summer   | 2010 | Painter  | 514         | A            |
| CS-101    | 1      | Fall     | 2009 | Packard  | 101         | H            |
| CS-101    | 1      | Spring   | 2010 | Packard  | 101         | F            |
| CS-190    | 1      | Spring   | 2009 | Taylor   | 3128        | E            |
| CS-190    | 2      | Spring   | 2009 | Taylor   | 3128        | A            |
| CS-315    | 1      | Spring   | 2010 | Watson   | 120         | D            |
| CS-319    | 1      | Spring   | 2010 | Watson   | 100         | B            |
| CS-319    | 2      | Spring   | 2010 | Taylor   | 3128        | C            |
| CS-347    | 1      | Fall     | 2009 | Taylor   | 3128        | A            |
| EE-181    | 1      | Spring   | 2009 | Taylor   | 3128        | C            |
| EE-302    | 1      | Summer   | 2010 | Watson   | 327         | C            |
| FIN-201   | 1      | Spring   | 2010 | Packard  | 101         | B            |
| HIS-351   | 1      | Spring   | 2010 | Painter  | 514         | C            |
| MU-199    | 1      | Spring   | 2010 | Packard  | 101         | D            |
| PHY-101   | 1      | Fall     | 2009 | Watson   | 100         | A            |
+-----------+--------+----------+------+----------+-------------+--------------+

换句话说，我想找到2009年秋季学期教授的所有课程，但不是2010年 Spring 学期。

sql mysql except

来源：https://stackoverflow.com/questions/52043848/mysql-except-equivalent

4条答案

按热度按时间

wztqucjr1#

mysql不支持 except ，所以就用 not exists 或者 not in :

select courseid
from section sf
where semester = 'Fall' and year = 2009 and not exists
      (select 1
       from section ss
       where sf.courseid = ss.courseid and ss.semester = 'Spring' and ss.year = 2010
      );

（我更喜欢 not exists 因为它对 NULL 值。）
这不准确，因为 except 删除重复项。你可以用 select distinct 但我怀疑这是否真的需要。

赞(0）回复(0）举报 2021-06-21

iszxjhcz2#

使用 not in 因为 except 在mysql中不可用

select *
from section 
where courseid not in 
(
select courseid
from section
where semester = 'Spring' and year= 2010
) and semester = 'Fall' and year = 2009

赞(0）回复(0）举报 2021-06-20

w6mmgewl3#

我会用 not exists :

select s.*
from section s
where semester = 'Fall' and year = 2009 and
      not exists (select 1 
                  from section s1
                  where s.course_id = s1.course_id and 
                        s1.semester = 'Spring' and s1.year = 2010
                 );

赞(0）回复(0）举报 2021-06-20

bf1o4zei4#

如果可能的话，我喜欢使用连接，在这里这当然是可能的。

SELECT sFa.course_id
FROM section AS sFa
LEFT JOIN section AS sSpr 
   ON sFa.course_id = sSpr.course_id
   AND sSpr.semester = 'Spring' AND sSpr.year= 2010
WHERE sFa.semester = 'Fall' 
   AND sFa.year= 2009
   AND sSpr.course_id IS NULL
;

赞(0）回复(0）举报 2021-06-20

我来回答

mysql，同等版本除外

4条答案

相关问题

热门标签

最新问答