mysql - 查询具有两个条件的内连接？

Question

我有这个 sql 查询，它选择在表中按 movie_name 找到的重复项:

SQL:

SELECT movies.movie_name, movies.year FROM movies
INNER JOIN (SELECT movie_name FROM movies
GROUP BY movie_name HAVING count(movie_id) > 1) dup ON movies.movie_name = dup.movie_name
// want also to test for same year, not just movie_name i.e movies.year = dup.year

这可能吗？

Answer 1

SELECT movies.movie_name, movies.year FROM movies
INNER JOIN (SELECT movie_name, year FROM movies
GROUP BY movie_name,year HAVING count(movie_id) > 1) dup ON movies.movie_name = dup.movie_name
and movies.year = dup.year

这似乎是一个合理的开始......

删除一个我认为您的意思是保留一个，不要忘记您可能有多个重复

假设我们将保留第一个，去掉其余的，并且具有最早 movie_id 的那个是第一个

所以

Select Min(Movie_id), Movie_Name, Year From Movies Group By Movie_Name, Year

会给你所有要保留的

Select Movie_id,Movie_Name,Year From Movies m 
Left Join 
(Select Min(Movie_id), Movie_Name, Year From Movies Group By Movie_Name, Year) keep
On keep.movieid = m.movieid Where keep.Movie_Id is null

上面是电影中的所有记录，但不在我们想要保留的所有记录的查询中。

这给了我们所有你想要摆脱的东西。看在克苏鲁的份上，不要相信我或你自己!删除之前请先备份!

Delete m From Movies m 
Left Join 
(Select Min(Movie_id), Movie_Name, Year From Movies Group By Movie_Name, Year) keep
On keep.movieid = m.movieid Where keep.Movie_Id is null

现在我们已经证明了查询(您确实证明了它不是您!)，我们不是选择有问题的记录，而是删除它们。

别忘了备份!