Mysql JOIN计数 [英] Mysql JOIN counts

问题描述

我有2个mysql表:

I have 2 mysql tables :

Question with the following columns : id, question, nranswers

答案必须是1到5之间的数字

Nranswers must be a number from 1 to 5

另一个表是

Answers with the following columns: questionid, userid, answer .

现在的问题是我想获得一个问题的每个答案的答复(假设ID为22).

Now the problem is that I want to get the replies for each answer for one question(id 22 let's say) .

P.S.如果nranswers为3，则结果应如下所示:

P.S. If the nranswers is 3, the result should look like this:

(正确的数字表示选择了答复号码的次数)

(the right number means how many times the reply number was chosen)

1-2-

2-8

3-7

如果nranswers为5，则结果应如下所示:

If the nranswers is 5, the result should look like this:

1-3-

2-8

3-14

4-19

5-8

请帮我解决这个问题，atm他不会计算未选择的答案，只会计算至少一次被选择的答案.

Please help me out with the query, atm he's not counting the answers that weren't chosen, only the ones that were chosen at least one time.

推荐答案

我随意添加了question_id列，该列将用于将每个答案与一个问题联系起来.

I took the liberty of adding the question_id column that will be used to join each answer with a question.

Question with the following columns : id, question, nranswers
Answers with the following columns: question_id, userid, answer

这是您的查询:

SELECT answer, COUNT(*) AS answer_count
FROM Answers
GROUP BY answer
WHERE question_id = 22
ORDER by answer

但是，如果nranswers为3，但是没有人选择3作为答案，则不会显示.该查询仅显示选择的答案.

However, if nranswers is 3, but nobody picked 3 as their answer, it won't show. This query only shows the answers that were chosen.

要获得所有可用答案的计数，而不仅仅是选定的答案，最简单的方法(明智的查询)是摆脱Question.nranswers列并添加表QuestionAnswers:

To get a count of all available answers, not just the selected ones, the simplest way (query wise) would be to get rid of the Question.nranswers column and add the table QuestionAnswers:

QuestionAnswers with the following columns: question_id, answer

QuestionAnswers中的数据如下:

The data in QuestionAnswers would like this:

quesiton_id answer
-------------------
22          1
22          2
22          3

因此，您将列出每个问题的所有可能答案.

So, you'd have all the possible answers listed for each question.

查询将是这样:

SELECT qa.answer, COUNT(a.question_id) AS answer_count
FROM QuestionAnswers qa
LEFT OUTER JOIN Answers a
  ON qa.question_id = a.question_id AND a.answer = qa.answer
GROUP BY qa.answer
WHERE qa.question_id = 22
ORDER by qa.answer

这篇关于Mysql JOIN计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！