mysql - 如何对来自不同表的汇总列进行分组？

Question

我有一个用户表 (User) 和 3 个教程表(文本、视频和其他)。

每个教程都有列 rating_positive 和 rating_negative并链接到用户 (id)。

我想选出教程最多的10个用户和总和他们的教程的正面/负面评级。

我尝试了以下查询，但它不起作用。它为 tutorials_count/pos/neg 返回太多结果。我怎样才能正确地做到这一点？

SELECT  
    u.id AS user_id,  
    (COUNT(t.id) + COUNT(v.id) + COUNT(o.id)) AS tutorials_count,
    (SUM(t.rating_positive) + SUM(v.rating_positive) + SUM(o.rating_positive)) AS pos,
    (SUM(t.rating_negative) + SUM(v.rating_negative) + SUM(o.rating_negative)) AS neg
FROM
    user u LEFT JOIN trick t ON u.id = t.submitter_id
    LEFT JOIN video v ON u.id = v.submitter_id
    LEFT JOIN other o ON u.id = o.submitter_id
GROUP BY u.id
ORDER BY tutorials_count DESC
LIMIT 10

Answer 1

尝试使用您感兴趣的三个表的 UNION ALL 进行子查询，然后加入:

SELECT  
    u.id AS user_id,  
    COUNT(submitter_id) AS tutorials_count,
    IFNULL(SUM(rating_positive), 0) AS pos,
    IFNULL(SUM(rating_negative), 0) AS neg
FROM user u
LEFT JOIN (
    SELECT submitter_id, rating_positive, rating_negative FROM trick
    UNION ALL
    SELECT submitter_id, rating_positive, rating_negative FROM video
    UNION ALL
    SELECT submitter_id, rating_positive, rating_negative FROM other
) T1
ON u.id = T1.submitter_id
GROUP BY u.id
ORDER BY tutorials_count DESC
LIMIT 10