mysql - 根据另一列获取最小出现次数

Question

问题:最少出演多少部电影？

表:

title | year | person | role (actor, director, writer, producer)

我做了什么:

SELECT title, count(role) 
FROM movie_table 
where role='actor' 
GROUP BY title 
ORDER by count(role) ASC;

我得到了什么:

title (007, Dark Knight, Superman, Batman ...) | count(role) (1, 1, 2, 2,...)

我需要的是一种方法来计算具有最少 Actor 角色的电影，在本例中为 2(007 和黑暗骑士)。

Answer 1

不太清楚你想要什么，但看起来你在要求这个:

SELECT title, count(role) 
FROM movie_table 
where role='actor' 
GROUP BY title 
HAVING count(role) = 1
ORDER by count(role) ASC;

根据您的评论，您可以使用如下内容:

SELECT title
FROM movie_table 
where role='actor' 
GROUP BY title 
HAVING count(role) = (select min(cnt)
                      from (select count(role) cnt
                            from movie_table
                            group by title) c)
ORDER BY count(role);

参见SQL Fiddle with Demo

如果您只需要总数，请将 count() 函数应用于查询:

select count(title) Total
from
(
  SELECT title
  FROM movie_table 
  where role='actor' 
  GROUP BY title 
  HAVING count(role) = (select min(cnt)
                        from (select count(role) cnt
                              from movie_table
                              group by title) c)
) x

参见SQL Fiddle with Demo