mysql - 向现有查询添加连接和计数会膨胀求和和计数函数输出

Question

我有这个工作正常的查询:

select problems.problem_id , problem_title , sum( vote ) as totalVotes 
from problems 
left join problem_votes on 
problems.problem_id = problem_votes.problem_id 
left join problem_categories on 
problems.problem_id = problem_categories.problem_id  
where problem_categories.category_id = 1 GROUP BY problem_title;  

当我在 suggested_solutions 表和一个 sum(column_in_suggested_solutions_table) 上添加另一个联接以查看针对某个问题有多少建议解决方案时，查询如下所示:

select problems.problem_id , problem_title , sum( vote ) as totalVotes , count(solution_name) from problems 
left join problem_votes on 
problems.problem_id = problem_votes.problem_id 
left join problem_categories on 
problems.problem_id = problem_categories.problem_id  
left join suggested_solutions on
problems.problem_id = suggested_solutions.problem_id
where problem_categories.category_id = 1 GROUP BY problem_title;

第二个查询的问题在于，虽然它仍然返回相同的行数，但原始的 sum() 函数返回了夸大的计数，而 suggested_solutions 的计数函数也返回了夸大的数字。

知道我在这里做错了什么吗？

谢谢!

Answer 1

特定 problem_id 有多个 suggested_solutions 行，这意味着原始查询中求和的行被夸大了。

您可以在派生表中执行分组依据，然后加入该表。

SELECT problems.problem_id,
       problem_title,
       SUM(vote) AS totalVotes,
       Solution_Count
FROM   problems
       LEFT JOIN problem_votes
         ON problems.problem_id = problem_votes.problem_id
       LEFT JOIN problem_categories
         ON problems.problem_id = problem_categories.problem_id
       LEFT JOIN (SELECT COUNT(solution_name) AS Solution_Count,
                         problem_id
                  FROM   suggested_solutions
                  GROUP  BY problem_id) ss
         ON problems.problem_id = ss.problem_id
WHERE  problem_categories.category_id = 1
GROUP  BY problem_title;  

请注意，您的 WHERE problem_categories.category_id = 1 也会在 problem_votes 和 problems 之间转换第一个 LEFT JOIN到 INNER JOIN