从一个表中获取全部，从另一个表中获取COUNT [英] Get all from one table and COUNT from another

问题描述

K，所以我有两个表:

K, so I have two tables:

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).

I want to get all from categories and count the number of questions (question_id) on each category.

说，第一个类别，计费，将有一个问题，第二个类别，安全性，将有3个问题.

Say, the first category, billing, would have 1 question and the second one, security, would have 3 questions.

我已经尝试过:

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

推荐答案

您要执行以下操作:

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将确保没有问题的类别以count = 0列出

The LEFT JOIN will make sure that categories with no questions get listed with count = 0

这篇关于从一个表中获取全部，从另一个表中获取COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！