本文介绍了从一个表中获取全部,从另一个表中获取COUNT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!