基于movies和people表查询作为principal actor参演最多电影的演员方法

我们分三步实现需求，同时兼容多个演员并列参演数第一的场景：

1. 先从people表过滤出所有角色为principal actor的记录
2. 按演员姓名分组统计每个演员的参演次数，找到最高的参演次数值
3. 匹配到对应次数的演员，再关联movies表拿到所需的字段内容

格式2实现（统计参演次数，逻辑更简单） #

直接返回参演数最多的演员和对应参演次数：

SELECT 
  person_name,
  COUNT(movie_id) AS `count (as principal actor)`
FROM people
WHERE role = 'principal actor'
GROUP BY person_name
HAVING COUNT(movie_id) = (
  -- 内层子查询：拿到最高的主演参演次数
  SELECT COUNT(movie_id) AS cnt
  FROM people
  WHERE role = 'principal actor'
  GROUP BY person_name
  ORDER BY cnt DESC
  LIMIT 1
)

代码说明 #

• 内层子查询先统计每个主演的参演次数，倒序排序后取第一条拿到最高次数值
• 外层查询统计每个主演的次数，通过HAVING筛选出次数等于最高值的演员，支持并列第一的场景

格式1实现（返回所有对应电影详细记录） #

需要关联movies表拿到电影名称字段：

SELECT 
  m.movie_name,
  p.person_name,
  p.role
FROM people p
JOIN movies m ON p.movie_id = m.movie_id
WHERE 
  p.role = 'principal actor'
  AND p.person_name IN (
    -- 中间层子查询：拿到所有参演数最多的主演姓名
    SELECT person_name
    FROM people
    WHERE role = 'principal actor'
    GROUP BY person_name
    HAVING COUNT(movie_id) = (
      -- 最内层子查询：拿到最高的主演参演次数
      SELECT COUNT(movie_id) AS cnt
      FROM people
      WHERE role = 'principal actor'
      GROUP BY person_name
      ORDER BY cnt DESC
      LIMIT 1
    )
  )

代码说明 #

• 最内层子查询先拿到最高的参演次数
• 中间层子查询筛选出所有参演次数等于最高值的演员姓名
• 外层查询过滤出这些演员的主演记录，关联movies表拿到电影名称返回

初学者常见报错原因 #

你之前写子查询报错大概率是这几个问题：

• 子查询返回多列，但外层用=判断，比如内层查了person_name,count两个字段，外层HAVING count = (子查询)，导致类型不匹配
• 没有先过滤role = 'principal actor'就统计，把导演、制片的记录也算进去了
• 别名冲突，或者自定义的别名带特殊字符没有加反引号包裹，比如count (as principal actor)这种别名要加反引号

内容的提问来源于stack exchange，提问作者user17351350