从一个表中获取所有数据并从另一个表中计算数量

Question

好的，我有两个表：

categories
+----+----------+
| id | slug     |
+----+----------+
| 1  | billing  |
| 2  | security |
| 3  | people   |
| 4  | privacy  |
| 5  | messages |
+----+----------+

categories_questions
+------------------+-------------+
| id | question_id | category_id |
+------------------+-------------+
| 1  |           1 |           2 |
| 2  |           2 |           5 |
| 3  |           3 |           2 |
| 4  |           4 |           4 |
| 5  |           4 |           2 |
| 6  |           5 |           4 |
+------------------+-------------+

我希望获取所有类别并计算每个类别中问题数（question_id）。 例如，第一个类别billing有1个问题，第二个类别security有3个问题。 我尝试过以下方法：

SELECT categories.*, count(categories_questions.id) AS numberOfQuestions
FROM categories
INNER JOIN categories_questions
ON categories.id = categories_questions.category_id

- weare1

Answer 1

你想要做到这一点：

SELECT categories.id, max(categories.slug), count(categories_questions.id) AS numberOfQuestions
FROM categories
LEFT JOIN categories_questions
ON categories.id = categories_questions.category_id
group by categories.id

LEFT JOIN会确保没有问题的类别也会被列出，计数为0。

- Adriano Carneiro

Answer 2

字符串 p_query = "select c.* , count(o.id) from CustomerTable c , OrderTable o where c.id=o.customerId GROUP BY c.id";

希望这能帮到你。谢谢。

- Happy Singh

Answer 3

应该可以了...在计数之前，你只需要聚合一些东西：

SELECT categories.*, COUNT(categories_questions.id) AS numberOfQuestions FROM categories
INNER JOIN categories_questions
ON categories.id = categories_questions.category_id
GROUP BY categories.id

- Dancrumb