如何计数和回声值

Question

我有一个包含这两个表的数据库-问题和答案。

表定义

questions - id, question, asked_by
answers - id, question_id(foreign key), answers, sent_by

我想数一数，并附和每个问题的总答案数。

样本数据

question
id   question   asked_by
1    how r u    emma
2    r u ok     sam

answer
id   question_id answer sent_by
1        2       good    john
2        1       fine    sam
3        2       WTG     biggie

在显示所有问题的页面上，我想按每个问题和答案的数量进行书写。问题2=2和问题1=1的答案数量。

Answer 1

尝试这种方式，首先COUNT，JOIN，然后GROUP BY

    SELECT q.question,count(*) AS answers FROM question q INNER JOIN answer a ON q.id=a.question.id
GROUP BY q.id

Answer 2

您应该使用内部联接，然后使用分组依据子句对每个问题进行计数条目：

SELECT t1.id
     , COUNT(*)
FROM question t1
JOIN answer t2 ON t1.id = t2.question_id
GROUP BY t1.id