代码之家 › 专栏 › 技术社区 › richards

SQL内部联接2表:基于组的平均值

sqlite sql

0

richards · 技术社区 · 7 年前

我有两张表:电影和演员表,模式如下:

movies: | id | name | score |
cast:   | movie_id | cast_id | cast_name | 
-- cast.movie_id corresponds with movies.id

我想在某些条件下找到平均电影分数最高的演员(分数为<40的电影应被忽略,选定的演员应出现在>2部电影中)。这就是我现在所拥有的:

SELECT c.cast_id, c.cast_name, AVG(m.score) AS average_score FROM movies AS m
INNER JOIN 
    (SELECT * FROM cast GROUP BY cast_id HAVING COUNT(cast_id) > 2) AS c
ON m.id == c.movie_id
GROUP BY c.cast_id
HAVING m.score >= 40
ORDER BY AVG(m.score) DESC, c.cast_name ASC
LIMIT 10;

但是当我检查结果时,例如对于一些演员,输出平均分是100.0,但是他的电影的分数是100,100,85。我可以知道我在上面的SELECT语句中哪里出错了吗?

1 回复 | 直到 7 年前

1

0

Gordon Linoff 7 年前

您可以将其表述为:

SELECT c.cast_id, c.cast_name, AVG(m.score) AS average_score 
FROM movies m INNER JOIN
     cast c
     ON m.id = c.movie_id INNER JOIN
     (SELECT c.cast_id
      FROM cast c
      GROUP BY c.cast_id 
      HAVING COUNT(*) > 2  -- Only need `COUNT(DISTINCT c.movie_id)` if a person can be in one movie more than once
     ) cc
     ON cc.cast_id = c.cast_id
WHERE m.score >= 40
GROUP BY c.cast_id, c.cast_name
ORDER BY average_score DESC, c.cast_name
LIMIT 10;

笔记:

子查询用于统计给定演员的电影。
JOIN 到 cast 需要获取id和名称。
这个 HAVING