如何限制最大计数
如何限制最大计数
提问于 2018-06-01 20:09:58
我想找到每一种类型的电影，找到N个在大多数类型的电影中扮演角色的演员
Tables and their columns:
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 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;但是我想把演员的数量限制在1个，那么我该怎么做呢？
示例：
我得到的结果是：
genre_name | actor_id | max_value
==================================
Thriller | 22591 | 7
Drama | 22591 | 6
Crime | 65536 | 3
Horror | 22591 | 3
Action | 292028 | 3
Action | 378578 | 3
Action | 388698 | 3我想要的结果：
genre_name | actor_id | max_value
==================================
Thriller | 22591 | 7
Drama | 22591 | 6
Crime | 65536 | 3
Horror | 22591 | 3
Action | 292028 | 3回答 4
Stack Overflow用户
发布于 2018-06-01 20:26:47
如果只想随机选择一个参与者，只需在代码中添加以下代码行：
select genre_name, actor_id, 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
USING(genre_name,max_value)
GROUP BY genre_name, max_value
ORDER BY max_value desc;Stack Overflow用户
发布于 2018-06-01 21:15:01
您使用的一些连接是多余的。
SELECT
U.genre_name, U.actor_id, U.actor_genre_count
FROM
(SELECT
A.genre_id, A.genre_name, C.actor_id, count(*) actor_genre_count
FROM genre A
JOIN movie_has_genre B
ON A.genre_id=B.genre_id
JOIN role C
ON C.movie_id=B.movie_id
GROUP BY A.genre_id, A.genre_name, C.actor_id) U
JOIN
(SELECT
S.genre_id, S.genre_name, MAX(S.actor_genre_count) max_actor_genre
FROM
(SELECT
A.genre_id, A.genre_name, C.actor_id, count(*) actor_genre_count
FROM genre A
JOIN movie_has_genre B
ON A.genre_id=B.genre_id
JOIN role C
ON C.movie_id=B.movie_id
GROUP BY A.genre_id, A.genre_name, C.actor_id) S
GROUP BY S.genre_id, S.genre_name) V
ON U.genre_name=V.genre_name AND U.actor_genre_count=V.max_actor_genre;Stack Overflow用户
发布于 2018-06-01 22:03:38
此解决方案改编自this Stack Overflow answer关于按名称限制结果的内容。我尝试执行一个类似的查询，即选择第一个actor_id并只返回它。
SELECT id, CategoryName, image, date_listed, item_id
SELECT t1.genre_name, t1.actor_id, t1.actor_movie_count
FROM
(
SELECT g.genre_name, r.actor_id, COUNT(*) as actor_movie_count
FROM genre g
INNER JOIN movie_has_genre mhg ON mhg.genre_id = g.genre_id
INNER JOIN role r ON m.movie_id = r.movie_id
GROUP BY g.genre_name, r.actor_id
) t1
LEFT JOIN
(
SELECT genre_name, actor_id, MAX(actor_movie_count) AS max_actor_movie_count
FROM
(
SELECT g.genre_name, r.actor_id, COUNT(*) AS actor_movie_count
FROM genre g
INNER JOIN movie_has_genre mhg ON mhg.genre_id = g.genre_id
INNER JOIN role r ON m.movie_id = r.movie_id
GROUP BY g.genre_name, r.actor_id
)
GROUP BY genre_name
) t2
ON t1.genre_name = t2.genre_name AND t1.actor_movie_count = t2.max_actor_movie_count AND (t1.actor_id > t2.actor_id)
WHERE t2.genre_id IS NULL
ORDER BY t1.actor_movie_count DESC如果这仍然不能解决您的问题，下面将介绍其他类似的问题和解释：
所以answer about returning 1 row per group
所以question about limiting query answer to N results per group
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50643139
复制相关文章
相似问题
腾讯云开发者
