2  

You can pivot the data by using an aggregate function with a CASE expression. You can use user-defined variables to implement a row number on each row by question. Your code will be similar to this:

您可以使用带有CASE表达式的聚合函数来透视数据。您可以使用用户定义的变量对每一行按问题实现行号。您的代码将类似于:

select q.text Question,
  max(case when a.is_correct = 1 then a.text end) Correct_answer,
  max(case when a.is_correct = 0 and rn=1 then a.text end) Incorrect_Answer1,
  max(case when a.is_correct = 0 and rn=2 then a.text end) Incorrect_Answer2,
  max(case when a.is_correct = 0 and rn=3 then a.text end) Incorrect_Answer3
from question q
inner join
(
  select a.id_question,
    a.text,
    a.is_correct,
    a.id_answer,
    @row:=case 
            when @prevQ=id_question
              and is_correct = 0
            then @row +1 
            else 0 end  rn,
    @prevA:=id_answer,
    @prevQ:=id_question
  from answer a
  cross join (select @row:=0, @prevA:=0, @prevQ:=0)r
  order by a.id_question, a.id_answer
) a
  on q.id_question = a.id_question
group by q.text
order by a.id_question, a.id_answer

See SQL Fiddle with Demo. This gives the result in separate columns:

参见SQL小提琴演示。这就得到了单独列的结果:

|   QUESTION | CORRECT_ANSWER | INCORRECT_ANSWER1 | INCORRECT_ANSWER2 | INCORRECT_ANSWER3 |
-------------------------------------------------------------------------------------------
| Question 1 |     Answer 1-1 |        Answer 1-2 |        Answer 1-3 |        Answer 1-4 |
| Question 2 |     Answer 2-1 |        Answer 2-2 |        Answer 2-3 |        Answer 2-4 |

1  

Constructing a dynamic pivot query for this is a lot of work. Instead, what I would probably do is use MySQL's GROUP_CONCAT() aggregate function to create a comma-separated list of the Incorrect_answer fields, while separating out the Correct_Answer as its own column:

为此构造一个动态的pivot查询需要大量的工作。相反，我可能要做的是使用MySQL的GROUP_CONCAT()聚合函数创建Incorrect_answer字段的逗号分隔列表，同时将Correct_Answer作为自己的列:

SELECT
  question.`text`,
  /* Separate the one where Is_Correct = 1 and discard the others with a MAX() aggregate */
  MAX(CASE WHEN Is_Correct = 1 THEN answer.`text` ELSE NULL END) AS Correct_Answer,
  /* Group the rows where Is_Correct = 0 into a comma-separated list */
  GROUP_CONCAT(CASE WHEN Is_Correct = 0 THEN answer.`text` ELSE NULL END) AS Incorrect_answers
FROM
  question
  JOIN answer ON question.id_question = answer.id_question
GROUP BY Question.`text`

The result this produces as received by your application code looks like:

您的应用程序代码接收到的结果如下:

Question     Correct_Answer Incorrect_answers
--------------------------------------------------------------
Question 1    Answer 1-1    Answer 1-2,Answer 1-3,Answer 1-4
Question 2    Answer 2-1    Answer 2-2,Answer 2-3,Answer 2-4

It then becomes trivial in your application code to split the Incorrect_answers column on the , since it is a comma-separated list.

然后，分割Incorrect_answers列在应用程序代码中就变得很简单了，因为它是一个逗号分隔的列表。

In PHP for example, something like :

在PHP中，比如:

$incorrect = explode(',', $row['Incorrect_answers']);

Or in Ruby or Python:

或者用Ruby或Python:

incorrect = incorrect_answers.split(',')