使用具有内部联接的组来查找列的平均值，然后对组进行筛选

Question

我一直在尝试解决一个sqlite问题，其中有两个表：Movies和movie_cast。

电影有列：id、movie_title和`score。以下是数据示例：

• 11|星球大战|76.496
• 62 | 2001：太空漫游| 39.064
• 152|开始徒步旅行|26.551

movie_cast具有列：movie_id、cast_id、cast_name、birthday、popularity。这是一个样品。

• 11|2|Mark Hamill |9/15/25|15.015

• 11 | 3 | Harrison Ford | 56年10月21日| 8.905

• 11 | 5 | Peter Cushing | 2013年5月26日| 6.35

在这种情况下，movies.id和movie_cast.movie_id是相同的。问题是找出平均电影评分最高的前十名演员。不包括分数<25。▪不包括出演过两部或两部以下电影的演员。

我的问题如下，但似乎没有得到正确的答案。

SELECT movie_cast.cast_id, 
movie_cast.cast_name, 
printf("%.2f",CAST(AVG(movies.score) as float)), 
COUNT(movie_cast.cast_name)
FROM movies
INNER JOIN movie_cast ON movies.id = movie_cast.movie_id
WHERE movies.score >= 25
GROUP BY movie_cast.cast_id
HAVING COUNT(movie_cast.cast_name) > 2 
ORDER BY AVG(movies.score ) DESC, movie_cast.cast_name ASC
LIMIT 10  

我得到的答案的格式是cast_id、cat_name、avg score。

-例如：3哈里森·福特52.30

我分析了又重新分析了我的逻辑，但都无济于事。我不确定哪里出了问题。任何帮助都会很棒！非常感谢。

Answer 1

这就是我编写查询的方式：

SELECT mc.cast_id, 
mc.cast_name, 
PRINTF('%.2f', AVG(m.score)) avg_score
FROM movie_cast mc INNER JOIN movies m 
ON m.id = mc.movie_id
WHERE m.score >= 25
GROUP BY mc.cast_id, mc.cast_name
HAVING COUNT(*) > 2
ORDER BY AVG(m.score) DESC, mc.cast_name ASC
LIMIT 10;

我为表使用别名来缩短代码并使其更可读
不需要将平均值强制转换为浮点值，因为SQLite中的平均值始终是实数
两个COUNT(movie_cast.cast_name)都可以简化为COUNT(*)，但您的需求不需要SELECT列表中的第一个(如果需要，则添加它(
函数PRINTF()返回一个字符串，但如果希望返回一个数字，则使用ROUND():

ROUND(AVG(m.score), 2) avg_score