mysql - MySQL正确加入计数不正常

我有以下表格：
电影

        +-------------+-------------+------+-----+---------+----------------+
        | Field       | Type        | Null | Key | Default | Extra          |
        +-------------+-------------+------+-----+---------+----------------+
        | id          | int(11)     | NO   | PRI | NULL    | auto_increment |
        | user_id     | int(11)     | YES  | MUL | NULL    |                |
        | title       | varchar(40) | NO   |     | NULL    |                |
        | description | text        | NO   |     | NULL    |                |
        | created_at  | datetime    | YES  |     | NULL    |                |
        | updated_at  | datetime    | YES  |     | NULL    |                |
        +-------------+-------------+------+-----+---------+----------------+

MOVIE_VOTES

        +------------+------------+------+-----+---------+----------------+
        | Field      | Type       | Null | Key | Default | Extra          |
        +------------+------------+------+-----+---------+----------------+
        | id         | int(11)    | NO   | PRI | NULL    | auto_increment |
        | user_id    | int(11)    | YES  | MUL | NULL    |                |
        | movie_id   | int(11)    | YES  | MUL | NULL    |                |
        | like       | tinyint(1) | YES  |     | NULL    |                |
        | created_at | datetime   | YES  |     | NULL    |                |
        | updated_at | datetime   | YES  |     | NULL    |                |
        +------------+------------+------+-----+---------+----------------+

我想查询所有电影的喜欢数量。我有这个查询：

        SELECT *, COUNT(*) AS count_likes
        FROM movie_votes
        RIGHT JOIN movies
        ON movies.id=movie_votes.movie_id
        WHERE movie_votes.like = 1
        GROUP BY movie_id
        ORDER BY count_likes;

这会返回按喜欢次数排序的电影，但不会返回尚未投票（喜欢）的电影。出于某种原因，RIGHT连接可以用作简单连接。
我已经搜索了一些相对的答案，但是找不到任何有用的答案。

最佳答案

在进行外部联接时，您应该指定一个列名，这样它将只计算非空值

SELECT  movies.id,
        movies.user_id,
        movies.title,
        movies.description,
        movies.created_at,
        movies.updated_at,
        COUNT(movie_votes.movie_id) AS count_likes
FROM    movies
        LEFT JOIN movie_votes
            ON  movies.id = movie_votes.movie_id
                AND movie_votes.like = 1
GROUP   BY movies.id,
        movies.user_id,
        movies.title,
        movies.description,
        movies.created_at,
        movies.updated_at
ORDER   BY count_likes

条件movie_votes.like = 1应该放在ON子句中，以便在将其联接到表movie_votes之前先过滤表movies中的行。

关于mysql - MySQL正确加入计数不正常，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25990707/