获取所有从另一个

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）的数量。

假设第一类帐单有1个问题，第二个帐号有3个问题。

我已经试过这样：

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

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

Answer 2

这应该这样做......你只需要计数之前的东西汇总：

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 

Answer 3

String p_query =“从CustomerTable c，OrderTable中选择c。*，count（o.id）o其中c.id = o.customerId GROUP BY c.id”;

希望这会帮助你。谢谢