mysql选择并计数,Mysql JOIN计数

I have 2 mysql tables :

Question with the following columns : id, question, nranswers

Nranswers must be a number from 1 to 5

And the other table is

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

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

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

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

1 - 3

2 - 8

3 - 14

4 - 19

5 - 8

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.

解决方案

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

Here's your query:

SELECT answer, COUNT(*) AS answer_count

FROM Answers

GROUP BY answer

WHERE question_id = 22

ORDER by answer

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.

Edit:

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

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.

The query would then be this:

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