如何用UNION或其他方式关联查询？用户统计结果异常求助

问题背景 #

需要统计每个用户对应的：

• article_history表中的行数（别名count）
• posts表中type='Q'的行数（别名count2）
• posts表中type='A'的行数（别名count3）
  并按这三个数值的平均值从高到低排序。

原查询的问题：

SELECT * 
FROM ( SELECT * FROM users) t1
JOIN ( SELECT COUNT(*) AS count2 FROM posts WHERE  `type` = 'Q' ) t2
JOIN ( SELECT COUNT(*) AS count3 FROM posts WHERE `type` = 'A' ) t3
JOIN ( SELECT COUNT(*) AS count FROM article_history) t4
ORDER BY (t4.count + t2.count2 + t3.count3) / 3 DESC

该查询返回所有用户的统计值完全相同，原因是子查询未按用户分组，且未与users表做用户维度的关联，统计的是全表总数，最终和用户表做了笛卡尔积，导致每个用户都拿到全表统计结果。

正确查询语句 #

假设三张表通过user_id字段关联（如果你的关联字段是其他名称，替换即可）：

SELECT
    u.username,
    COALESCE(ah.count, 0) AS count,
    COALESCE(pq.count2, 0) AS count2,
    COALESCE(pa.count3, 0) AS count3,
    (COALESCE(ah.count, 0) + COALESCE(pq.count2, 0) + COALESCE(pa.count3, 0)) / 3 AS avg_score
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS count
    FROM article_history
    GROUP BY user_id
) ah ON u.user_id = ah.user_id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS count2
    FROM posts
    WHERE type = 'Q'
    GROUP BY user_id
) pq ON u.user_id = pq.user_id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS count3
    FROM posts
    WHERE type = 'A'
    GROUP BY user_id
) pa ON u.user_id = pa.user_id
ORDER BY avg_score DESC;

关键说明 #

• 每个统计子查询都通过GROUP BY user_id按用户分组，仅统计当前用户的对应数据
• 使用LEFT JOIN确保即使某用户无对应记录（如未发布过Q类型帖子），仍能保留该用户行；COALESCE将统计结果为NULL的情况转为0，避免计算平均值时出现异常
• 直接计算平均值字段avg_score，排序逻辑更清晰直观

内容的提问来源于stack exchange，提问作者Junior