mysql - 具有多个连接的计数

Question

我设置了 3 个表格(项目、评论、评级)。我想统计每个项目的评分总数和评论总数，然后将它们合并为互动总数，然后根据互动输出排名靠前的项目。

我在查询时有点困难。

SELECT item.id, count(reviews.review_id) AStotal_reviews, count( ratings. rating_id) AStotal_ ratings,total_reviews+total_ ratings AStotal_interactions FROM items LEFT OUTER JOIN Reviews ON Reviews.item_id=item.id LEFT OUTER JOIN 评分 ON rating.item_id=item.id GROUP BY item.id ORDER BY Total_interactions DESC LIMIT 0,1

Answer 1

SELECT  *, total_reviews + total_ratings AS total_interactions
FROM    (
        SELECT  id,
                (
                SELECT  COUNT(*)
                FROM    reviews
                WHERE   item_id = i.id
                ) AS total_reviews,
                (
                SELECT  COUNT(*)
                FROM    ratings
                WHERE   item_id = i.id
                ) AS total_ratings
        FROM    items i
        ) q
ORDER BY
        total_interactions DESC
LIMIT 1