mysql - 添加 where 子句后计数查询停止正常工作

Question

我有 3 个表:问题、类别和 problem_categories 的连接表

我正在尝试获取并计算与问题相关的所有类别，并计算与每个问题相关的问题数量。

这是我的查询，它没有返回正确的结果:

SELECT 
  category_name , 
  categories.category_id , 
  problems.problem_id, 
  COUNT(problems.problem_id) as num_problems 
FROM 
  problem_categories 
left JOIN 
  categories 
on 
  problem_categories.category_id = categories.category_id 
left join 
  problems 
on
  problem_categories.problem_id = problems.problem_id 
WHERE 
  problem_categories.problem_id = 266
GROUP BY 
  problems.problem_id , category_name

但是一旦我取出 where 子句，它就会返回所有类别和正确的问题计数。但诀窍是我需要针对特定​​问题_id 查询它

有什么方法可以调整此查询使其正确吗？我在这里做错了什么？

谢谢!!

Answer 1

你的问题有点不清楚。如果您选择一个特定的问题，您如何计算问题？如果你想计算与特定问题相关的类别，你可以这样做(我假设你有外键并且在 problem_categories 表中没有重复项):

SELECT problem_id, COUNT(category_id) as num_categories
FROM problem_categories
WHERE problem_id = 266
GROUP BY problem_id

如果您需要打印出更多问题信息，则加入问题表(为了提高效率，总是最后获取详细信息)。如果你想计算问题 -> 类别 -> 问题然后做:

select p1.problem_id, (COUNT(DISTINCT(p2.problem_id)) - 1) as num_problems
FROM
(select problem_id, category_id FROM problem_categories where problem_id = 266) as p1
inner join 
(select problem_id, category_id FROM problem_categories) as p2
ON p1.category_id = p2.category_id
GROUP by p1.problem_id

同样，如果您想显示问题的详细信息，请加入问题表以获取它们。如果您希望 num_problems 和 num_categories 一起加入第一个查询到第二个查询，然后再加入问题表以获取问题详细信息。即:

SELECT p1.problem_id, p1.num_categories, p2.num_problems 
FROM
(SELECT problem_id, COUNT(category_id) as num_categories
FROM problem_categories
WHERE problem_id = 266
GROUP BY problem_id) as p1
INNER JOIN
(SELECT p1.problem_id, (COUNT(DISTINCT(p2.problem_id)) - 1) as num_problems
FROM
(SELECT problem_id, category_id FROM problem_categories where problem_id = 266) as p1
INNER JOIN 
(SELECT problem_id, category_id FROM problem_categories) as p2
ON p1.category_id = p2.category_id
GROUP by p1.problem_id) as p2
ON p1.problem_id = p2.problem_id