如何以Laravel方式合并用户提问数与回答数的统计查询并输出统一结果？

嘿，我来帮你搞定这个合并统计的需求！你已经有了分别统计提问和回答数量的查询，现在要把它们合并成一个统一输出，这里给你两种实用的方案，按需选择就行：

方案1：合并统计并区分提问/回答类型 #

如果你想在结果里明确区分每条记录是提问数还是回答数，可以用UNION ALL把两个统计结果合并，同时新增一个类型字段标记：

SELECT 
    userid,
    '提问' AS type,
    count AS num
FROM (
    SELECT tb_addquestion.userid, COUNT(*) AS count 
    FROM tb_addquestion 
    LEFT JOIN tb_user ON tb_user.userid = tb_addquestion.userid 
    GROUP BY tb_addquestion.userid 
    HAVING count > 0 AND count < 15
) AS question_counts
UNION ALL
SELECT 
    userid,
    '回答' AS type,
    count AS num
FROM (
    SELECT tb_answer.userid, COUNT(*) AS count 
    FROM tb_answer 
    LEFT JOIN tb_user ON tb_user.userid = tb_answer.userid 
    GROUP BY tb_answer.userid 
    HAVING count > 0 AND count < 15
) AS answer_counts;

注意：UNION ALL会保留所有符合条件的记录，比UNION更高效（因为不会去重），如果你的场景不需要去重就用它。

方案2：同一行展示用户的提问数和回答数 #

要是你想让每位用户的提问数和回答数在同一行显示，那就用左连接把两个统计结果关联起来，还可以用COALESCE把NULL值转换成0（避免没有提问/回答的用户显示空值）：

SELECT 
    u.userid,
    COALESCE(q.count, 0) AS question_count,
    COALESCE(a.count, 0) AS answer_count
FROM tb_user u
LEFT JOIN (
    SELECT userid, COUNT(*) AS count 
    FROM tb_addquestion 
    GROUP BY userid 
    HAVING count > 0 AND count < 15
) q ON u.userid = q.userid
LEFT JOIN (
    SELECT userid, COUNT(*) AS count 
    FROM tb_answer 
    GROUP BY userid 
    HAVING count > 0 AND count < 15
) a ON u.userid = a.userid
WHERE q.count IS NOT NULL OR a.count IS NOT NULL; -- 只保留有提问或回答的用户

这里我把原查询里的LEFT JOIN tb_user移到了外层，这样能确保所有用户都被考虑到，同时通过WHERE过滤掉既没有提问也没有回答的用户，和你原查询的逻辑一致。

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