mysql - 多个表上的 SQL 查询、AVG 和 COUNT

Question

我需要一个查询，我使用的查询由于某种原因不能按我想要的方式工作

这里是查询涉及的所有表。

这是我想要的查询:显示带有平均评分和推荐数量的图书列表

结果应该是这样的:

我已经尝试过的:

SELECT        book.isbn, AVG(ratings.rating) AS [AVG Ratings], COUNT(recommend.isbn) AS [Number of recommendation]
FROM            book INNER JOIN
                         recommend ON book.isbn = recommend.isbn INNER JOIN
                         ratings ON book.isbn = ratings.isbn
GROUP BY book.isbn

但它没有用，不知何故 AVG 评分效果很好，但推荐数量没有，它与评分表冲突。

结果如下:

然而，当我单独尝试每一个时，一切都像这样工作得很好:

对于平均评分:

SELECT        book.isbn, AVG(ratings.rating) AS [AVG Ratings]
FROM            book INNER JOIN
                         ratings ON book.isbn = ratings.isbn
GROUP BY book.isbn

结果如下:

对于推荐的数量:

SELECT        book.isbn, COUNT(recommend.isbn) AS [Number of recommendation]
FROM            book INNER JOIN
                         recommend ON book.isbn = recommend.isbn
GROUP BY book.isbn

结果如下:

所以我想要一个查询将两个 View 合并为一个 View

Answer 1

如果你想获得准确的结果，那么你需要在连接之前进行聚合:

SELECT b.isbn, r.AvgRating, re.NumRecommendation
FROM  book b LEFT JOIN
      (SELECT r.isbn, AVG(r.rating) as AvgRating
       FROM rating r
       GROUP BY r.isbn
      ) r
      ON b.isbn = r.isbn LEFT JOIN
      (SELECT r.isbn, COUNT(*) as NumRecommendation
       FROM recommendation r
       GROUP BY r.isbn
      ) re
      on b.isbn = r.isbn ;

请注意，我还切换到左外部联接，因此您将获得所有书籍的结果，即使是那些缺少评分或推荐的书籍。