K,是以我有兩張桌子:
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
解決方法:
你想這樣做:
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
标簽:sql,mysql,select
來源: https://codeday.me/bug/20190726/1543067.html