php - MySQL 分组依据，按两列排序

Question

我想获取每部电影的最后一集名称

表结构:

+---------+------------+---------------+
|     id  |  movie_id  |  episode_name |
+---------+------------+---------------+
|      1  |         1  |             1 |
|      2  |         1  |             2 |
|      3  |         1  |             3 |
|      4  |         2  |             1 |
|      5  |         3  |             1 |
|      6  |         2  |             2 |
|      7  |         1  |             4 |
|      8  |         2  |             3 |
|      9  |         4  |             1 |
+---------+------------+---------------+

查询 1:

SELECT * FROM episode
group by movie_id
order by id desc, episode_name desc LIMIT 10

查询 2:

SELECT * FROM episode
WHERE movie_id IN (select movie_id from episode group by movie_id)
group by movie_id
order by episode_name desc

但是输出不是我想要的，可能我做错了

+--------+------------+---------------+
|  id    |  movie_id  |  episode_name |
+--------+------------+---------------+
|     9  |         4  |             1 |
|     5  |         3  |             1 |
|     4  |         2  |             1 |
|     1  |         1  |             1 |
+--------+------------+---------------+

我想要的结果是:

+---------+------------+---------------+
|     id  |  movie_id  |  episode_name |
+---------+------------+---------------+
|      9  |         4  |             1 |
|      8  |         2  |             3 |
|      7  |         1  |             4 |
|      5  |         3  |             1 |
+---------+------------+---------------+

Answer 1

这是一个很常见的问题，我们可以在 MySQL 中通过将 episode 表连接到一个子查询来处理这个问题，该子查询会查找每部电影的最新一集。这种方法为我们提供了完整匹配的最新记录，包括 id 列，然后我们可以使用它来对结果集进行排序。

SELECT e1.*
FROM episode e1
INNER JOIN
(
    SELECT movie_id, MAX(episode_name) AS max_episode_name
    FROM episode
    GROUP BY movie_id
) e2
    ON e1.movie_id = e2.movie_id AND
       e1.episode_name = e2.max_episode_name
ORDER BY
    e1.id DESC;

Demo