我有两个表:

tb_question
**id_quest**    | **desc_quest**
    1           | How do you do...?
    2           | How are you...?

tb_answer
**id_quest** | **date_answer**
    1        |    2013/11/25
    1        |    2013/11/26
    1        |    2013/11/27


而我的查询:

SELECT
q.id_quest,
q.desc_quest,
COUNT(a.id_quest) as total_answer -- count total answer by question

FROM tb_question q
INNER JOIN tb_answer a
ON q.id_quest = a.id_quest;


结果:

ID_QUEST  | DESC_QUEST              | TOTAL_ANSWER
   1      | How do you do...?       |    3


如何返回还等于0的id = 2的问题?

预期结果:

ID_QUEST  | DESC_QUEST              | TOTAL_ANSWER
   1      | How do you do...?       |    3
   2      | How are you...?         |    0


http://sqlfiddle.com/#!2/3bfe7/1

最佳答案

两个问题:


您必须使用外部联接来获取没有答案的问题。
您省略了GROUP BY子句。




SELECT
    q.id_quest,
    q.desc_quest,
    COUNT(a.id_quest) as total_answer
FROM tb_question q
LEFT OUTER JOIN tb_answer a ON q.id_quest = a.id_quest
GROUP BY q.id_quest


DEMO

关于mysql - 查询还会返回值为0的列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/20249077/

10-13 04:47