试试这个查询：

SELECT
    username,
    SUM(reviews_count) AS total_reviews,
    SUM(ratings_sum) AS total_ratings,
    SUM(total_pages) AS total_pages
FROM (
    SELECT
        r.username,
        COUNT(DISTINCT r.review_id) AS reviews_count,
        SUM(rt.rating) AS ratings_sum,
        SUM(b.num_pages) AS total_pages
    FROM reviews r
    JOIN ratings rt ON r.book_id = rt.book_id AND r.username = rt.username
    JOIN books b ON r.book_id = b.book_id
    GROUP BY r.username, r.book_id
) AS subquery
GROUP BY username
ORDER BY total_reviews + total_ratings DESC
LIMIT 7;

total_ratings和total_reviews无法识别！将您的查询用作子查询，然后对其应用顺序和限制：

SELECT *
FROM (
  SELECT
    re.username,
    SUM(re.reviews_id) AS total_reviews,
    SUM(ra.rating) AS total_ratings,
    SUM(b.num_pages) AS total_pages
  FROM reviews re
  JOIN ratings ra
    ON re.book_id = ra.book_id
    AND re.username = ra.username
  JOIN books b
    ON b.book_id = re.book_id
  GROUP BY re.username
) AS s
ORDER BY total_reviews + total_ratings DESC
LIMIT 7;

解决这个问题的一种方法是在评论和评级之间进行FULL [OUTER] JOIN。

SELECT
    username,
    COUNT(*) AS total_books,
    COUNT(review_id) AS total_reviews,
    COUNT(rating_id) AS total_ratings,
    SUM(b.num_pages) AS total_pages,
    COUNT(review_id) + COUNT(rating_id) AS total_indicator
FROM reviews re
FULL JOIN ratings USING (book_id, username)
JOIN books b USING (book_id)
GROUP BY username
ORDER BY total_indicator DESC
LIMIT 7;

这里有一个db<>fiddle

我在我创建的示例表上测试了这个查询，它似乎工作正常。

SELECT u.username,
       COALESCE(SUM(r.rating), 0) AS total_ratings,
       COALESCE(COUNT(DISTINCT r.book_id), 0) AS rated_books,
       COALESCE(COUNT(DISTINCT rv.book_id), 0) AS reviewed_books,
       COALESCE(SUM(b.num_pages), 0) AS total_pages_reviewed
FROM (
    SELECT username FROM Ratings
    UNION
    SELECT username FROM Reviews
) AS u
LEFT JOIN Ratings r ON u.username = r.username
LEFT JOIN Reviews rv ON u.username = rv.username
LEFT JOIN Books b ON rv.book_id = b.book_id
GROUP BY u.username
ORDER BY (COALESCE(SUM(r.rating), 0) + COALESCE(COUNT(DISTINCT rv.book_id), 0)) DESC
LIMIT 7;