如何在同一行中选择多个字段？ （MySQL的）

I asked a question yesterday about using MySQL to tally results to a survey.

昨天我问了一个关于使用MySQL来计算调查结果的问题。

Now my question is, if I had a table of survey questions, a table of survey choices, and a table of users answers to those survey questions, how would I select the survey question along with all the choices for that survey within the same query?

现在我的问题是,如果我有一个调查问题表,一个调查选择表和一个用户对这些调查问题的答案表,我将如何在同一查询中选择调查问题以及该调查的所有选项?

Questions Table

question_id (int)
question (text)

Choices Table

choice_id (int)
question_id (int)
choice_text (varchar)

Answers Table

answer_id (int)
question_id (int)
choice_id (int)

What SELECT should I do to get the survey question along with all the choices for that survey (known or unknown amount) all in the same query? (if possible, also do the math, found in my other question, within the same query)

我应该做什么SELECT才能在同一个查询中获得调查问题以及该调查的所有选择(已知或未知数量)? (如果可能的话,也可以在同一个查询中找到我的其他问题中的数学运算)

I'm not so advanced with MySQL.

我不是那么先进的MySQL。

Thanks

EDIT: Sorry, What I meant was, I'm trying to get a SELECT statement to select the question, and all the choices corresponding to that question, in one row.

编辑:对不起,我的意思是,我正在尝试获取一个SELECT语句来选择问题,以及与该问题相对应的所有选项,在一行中。

If I do something like

如果我做的事情

SELECT question_id, question, choice_id, choice_text FROM questions LEFT JOIN choices USING(question_id)

I get multiple rows, one for each choice_id.

我得到多行,每个choice_id一行。

The results should be something like

结果应该是这样的

question     choice_1  choice_2  choice_3
A or B or C     A          B        C

The math part is tallying up the results to the survey, and yes, the choice_id is a PK, if that helps.

数学部分将结果统计到调查中,是的,如果有帮助,choice_id是PK。

4 个解决方案

#1

To get the questions and the choices for each question:

要获得每个问题的问题和选择:

SELECT question, choice_text
FROM questions
JOIN choices
ON questions.question_id = choices.question_id

Add LEFT before JOIN if you also want questions that have no choices.

如果您还想要没有选择的问题,请在JOIN之前添加LEFT。

To get the counts for each answer you could do this:

要获得每个答案的计数,您可以这样做:

SELECT question, choice_text, COUNT(answers.choice_id)
FROM questions
JOIN choices
    ON questions.question_id = choices.question_id
LEFT JOIN answers
    ON questions.question_id = answers.question_id
    AND choices.choice_id = answers.choice_id
GROUP BY questions.question_id, choices.choice_id
ORDER BY questions.question_id, choices.choice_id

To get the number of people that selected each answer as a percentage (per question) use the following query:

要获得以百分比(每个问题)选择每个答案的人数,请使用以下查询:

SELECT question, choice_text, COUNT(answers.choice_id) * 100 / questiontotal
FROM questions
JOIN (
        SELECT questions.question_id, COUNT(answers.choice_id) AS questiontotal
        FROM questions
        LEFT JOIN answers ON questions.question_id = answers.question_id
        GROUP BY questions.question_id
    ) AS answercounts
    ON questions.question_id = answercounts.question_id
JOIN choices ON questions.question_id = choices.question_id
LEFT JOIN answers
    ON questions.question_id = answers.question_id
    AND choices.choice_id = answers.choice_id
GROUP BY questions.question_id, choices.choice_id
ORDER BY questions.question_id, choices.choice_id;

Here's the testdata I used:

这是我使用的testdata:

CREATE TABLE questions (question_id int, question nvarchar(100));
INSERT INTO questions (question_id, question) VALUES
(1, 'Foo?'),
(2, 'Bar?');

CREATE TABLE choices (choice_id int, question_id int, choice_text nvarchar(100));
INSERT INTO choices (choice_id, question_id, choice_text) VALUES
(1, 1, 'Foo1'),
(2, 1, 'Foo2'),
(3, 1, 'Foo3'),
(4, 2, 'Bar1'),
(5, 2, 'Bar2');

CREATE TABLE answers (answer_id int, question_id int, choice_id int);
INSERT INTO answers (answer_id, question_id, choice_id) VALUES
(1, 1, 1),
(2, 1, 1),
(3, 1, 3),
(4, 2, 4),
(4, 2, 5);

And the output I get with the last query on this data:

我在上次查询数据时得到的输出:

'Foo?', 'Foo1', 66.6667
'Foo?', 'Foo2', 0.0000
'Foo?', 'Foo3', 33.3333
'Bar?', 'Bar1', 50.0000
'Bar?', 'Bar2', 50.0000

In the update to your question you say you want to return all the values for one question on one row. I would recommend that you do not try to do this, and instead use the method I have given you above. If you need to present the data in one row to your end-user, this can be done using PHP.

在您的问题更新中,您说您想要在一行中返回一个问题的所有值。我建议您不要尝试这样做,而是使用我上面给出的方法。如果需要将数据一行显示给最终用户,可以使用PHP完成。

#2

I would suggest Mark Byers's answer, though theoretically if you need them "all in one row" just for viewing purposes but not as part of a PHP routine you can do.

我建议使用Mark Byers的答案,虽然理论上如果你需要它们“全部在一行”只是为了查看目的而不是你可以做的PHP例程的一部分。

SELECT question, GROUP_CONCAT(choice_text) as choice_texts
FROM questions
JOIN choices
ON questions.question_id = choices.question_id
GROUP BY question;

The output would be

输出将是

'Foo?', 'Foo1,Foo2,Foo3'
'Bar?', 'Bar1,Bar2'

Note 'Foo1,Foo2,Foo3' would be returned as one column not three columns, and GROUP_CONCAT has a maximum of 1024 characters in its output by default though it can be increased. Its not recommended if the results might be large

注意'Foo1,Foo2,Foo3'将作为一列而不是三列返回,并且GROUP_CONCAT默认情况下在其输出中最多包含1024个字符,尽管可以增加它。如果结果可能很大,则不建议使用

#3

Note that the Answers table probably doesn't need the QuestionID column.

请注意,Answers表可能不需要QuestionID列。

This will get you the question text, choice text, and the number of answers per choice.

这将为您提供问题文本,选择文本和每个选项的答案数量。

SELECT Questions.question, Choices.choice_text, Count(Answers.*) AS N
FROM Questions INNER JOIN Choices ON Questions.question_id = Choices.question_id
     LEFT JOIN Answers ON Choices.choice_id = Answers.choice_id
GROUP BY Questions.question_id, Choices.choice_id

Edit: unless you're very, very good at SQL, it's probably best to forget about the "all one row" part - unless the number of choices per question is not only known, but constant, and even in that case, it ain't easy. It's really a display issue, so deal with it when you display the results.

编辑:除非你非常非常擅长SQL,否则最好忘记“所有一行”部分 - 除非每个问题的选择数量不仅是已知的,而且是恒定的,即使在这种情况下,它也是不容易。这确实是一个显示问题,因此在显示结果时要处理它。

#4

SELECT questions.question_id, questions.question, choices.choice_id, choices.question_id
FROM questions, choices 
WHERE questions.question_id = choices.question_id 
AND questions.question_id = "Something"

Should work, I think.

我认为应该工作。