mysql - 在带有子查询的删除语句中使用 EXISTS 代替 IN 运算符

Question

作为一名 SQL 外行，我正在做斯坦福大学 SQL 类(class)中的一些数据修改查询来进行练习。最后一个练习对我来说有点棘手，尽管我设法使用 IN 运算符完成了它。作为一个小附加组件，我尝试使用子查询的 EXISTS 重写查询。事实证明这比预期的要困难，所以现在我想知道是否有一种更优雅的方法。

关于 Q4:“删除电影年份在 1970 年之前或 2000 年之后的所有评分，且评分低于 4 星。”

练习:https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/courseware/ch-sql/seq-exercise-sql_movie_mod/?activate_block_id=i4x%3A%2F%2FDB%2FSQL%2Fsequential%2Fseq-exercise-sql_movie_mod

SQL fiddle 可以在这里找到:http://www.sqlfiddle.com/#!9/f7f3ad

提前非常感谢您!

我首先尝试将附加的 AND 语句拖到子查询中，然后仅将 IN 替换为 EXISTS。不久之后，我发现这实际上会删除整个表，因为 Exists 子句将评估为 true，而不引用外部表。

意识到这一点后，我尝试在外表中使用别名，以便能够从内部明确地引用外表。但是，系统不让我这样做。我相信他们正在运行 Postgres，而我们在 Uni 使用 MySQL。你知道MySQL是否允许在数据修改语句中使用别名吗？

现在是查询本身。对我来说，它看起来相当冗长。有没有更聪明的方法来使用 EXISTS 来解决这个问题？或者也许是完全不同的东西，根本不依赖于 IN/Exists。

使用IN:

Delete 
FROM Rating
Where MId IN 
(
/*movies either before 1970 or after 2000*/
    Select r.MId
    FROM Movie as m
    INNER JOIN Rating as r
    on m.MId = r.MId 
    WHERE m.year <1970 Or m.year > 2000
) 
-- pick those with less than 4 stars
AND stars < 4;

使用存在:

 Delete
 FROM Rating
 Where EXISTS(
     Select *
     FROM Movie as m
     INNER JOIN Rating as r
     on m.MId = r.MId 
     WHERE (m.year <1970 Or m.year > 2000) AND r.stars < 4
     AND Rating.rId = r.RId AND Rating.Mid = r.MId AND Rating.stars <4
);

让我苦苦挣扎的一件事是最后一点，我检查外表中的星星是否< 4。对我来说，看起来我正在连接条件内完成整个查询的工作。然而，我发现忽略这一点实际上会删除所有 rId 201 的评级，因为它发现第一个 201 元组符合标准(在日期范围之外，少于 4 颗星)，并得出结论，既然存在，它也可以删除其他元组(其中星星实际上 >= 4)

Answer 1

MySQL 不允许您在此类语句中引用正在更新或删除的表。就您而言，这很容易解决。

我更喜欢存在:

delete r from rating r
where exists (select 1
                from Movie m
                where m.MId = r.MId and
                      (m.year < 1970 or m.year > 2000)
             ) and
-- pick those with less than 4 stars
     r.stars < 4;

您可以使用 in 执行类似的操作:

delete r from rating r
where r.MId in (select m.MId
                from Movie m
                where m.year < 1970 or m.year > 2000
             ) and
-- pick those with less than 4 stars
     r.stars < 4;