时间戳

首页 > 解决方案 > 某列的分组最大值有问题

mysql - 某列的分组最大值有问题

问题描述

我想为每个类型的电影找到，找到在大多数类型的电影中扮演过的N个演员

表格及其列：

actor(actor_id,name)
role(actor_id,movie_id)
movie(movie_id,title)
movie_has_genre(movie_id,genre_id)
genre(genre_id,genre_name)

我开始这样做：

select genre.genre_name,actor.actor_id,count(genre.genre_name) as max_value from genre
inner join movie_has_genre on movie_has_genre.genre_id=genre.genre_id
inner join movie on movie_has_genre.movie_id=movie.movie_id
inner join role on movie.movie_id=role.movie_id
inner join actor on actor.actor_id=role.actor_id
group by genre.genre_name,actor.actor_id 
order by max_value desc;

这给了我每种类型的电影，演员以及他们播放过多少这种类型的电影，我想找到播放每种类型电影最多的演员，所以我尝试了这个：

 select genre.genre_name,actor.actor_id,count(genre.genre_name) 
from(select genre.genre_name,actor.actor_id,count(genre.genre_name) as max_value from genre
inner join movie_has_genre on movie_has_genre.genre_id=genre.genre_id
inner join movie on movie_has_genre.movie_id=movie.movie_id
inner join role on movie.movie_id=role.movie_id
inner join actor on actor.actor_id=role.actor_id
group by genre.genre_name,actor.actor_id 
order by max_value desc) as apotelesmata
group by genre.genre_name;

我从mysql工作台得到一个错误，它几乎不能识别我在外部选择函数上的所有内容。我的问题是我应该怎么做才能得到正确的结果。使用我给出的第一个代码，我得到了这个：

Thriller    22591   7
Drama   22591   6
Crime   65536   3
Horror  22591   3
Thriller    812916  3
Action  292028  3
Action  378578  3
Thriller    292028  3
Thriller    378578  3

但我想得到这个：

Thriller    22591   7
Drama   22591   6
Crime   65536   3
Horror  22591   3
Action  292028  3
Action  378578  3

标签： mysqlmysql-workbench

解决方案

一种选择，相当难看：

SELECT t1.genre_name, t1.actor_id, t1.max_value
FROM
(
    SELECT g.genre_name, a.actor_id, COUNT(*) AS max_value
    FROM genre g
    INNER JOIN movie_has_genre mhg ON mhg.genre_id = g.genre_id
    INNER JOIN movie m ON mhg.movie_id = m.movie_id
    INNER JOIN role r ON m.movie_id = r.movie_id
    INNER JOIN actor a ON a.actor_id = r.actor_id
    GROUP BY g.genre_name, a.actor_id
) t1
INNER JOIN
(
    SELECT genre_name, MAX(max_value) AS max_value
    FROM
    (
        SELECT g.genre_name, a.actor_id, COUNT(*) AS max_value
        FROM genre g
        INNER JOIN movie_has_genre mhg ON mhg.genre_id = g.genre_id
        INNER JOIN movie m ON mhg.movie_id = m.movie_id
        INNER JOIN role r ON m.movie_id = r.movie_id
        INNER JOIN actor a ON a.actor_id = r.actor_id
        GROUP BY g.genre_name, a.actor_id
    ) t
    GROUP BY genre_name
) t2
    ON t1.genre_name = t2.genre_name and t1.max_value = t2.max_value
ORDER BY
    t1.max_value DESC;

The verbosity of this answer has to do with that the base table for your question is actually a query already involving 4 joins. This table needs to be repeated in MySQL because we don't have analytic functions. Also, we don't have common table expressions, which could also make the query much more concise.

推荐阅读