我正在尝试从多个表中提取数据,并寻找一种解决方案,将提取的结果行转换为列。
table_students, table_questions, table_answers
table_students
+----+---------+------+---------+
| id | exam_id| name| teacher |
+----+---------+------+---------+
| 1 |123456 |John |George |
| 2 |6589974 |Nick |Harry |
| 3 |893258 |Michael|Thomas |
+----+---------+------+---------+
问题+----+------------+
| id | question |
+----+------------+
| 1 |Question one|
| 2 |Question two|
| 3 |Question three|
+----+-------------+
答案表格+----+----------------+-----------+------------------+
| id | exam_id |question_id| answer |
+----+----------------+-----------+------------------+
| 1 |123456 |1 | Answer given one|
| 2 |123456 |2 |Answer given two |
| 3 |123456 |3 |Answer given three|
| 4 |893258 |1 | Answer given two|
| 5 |893258 |2 |Answer given one |
| 6 |893258 |2 |Answer given one |
+----+-----------------+-----------+------------------+
需要的结果
+----+--------------+-------------+-------------+----------------------+
| id | exam_id |Question one | Question two| Question three |
+----+-----------------+-------------+-------------+----------------------+
| 1 |123456 |Answer given one |Answer given two |Answer given three|
| 2 |893258 |Answer given one | Answer given two|Answer given three|
+----+-----------------+-------------+-------------+----------------------+
尝试过
我尝试获取并将结果行转换为列,但没有成功。 我正在尝试使用这种格式 https://dba.stackexchange.com/questions/164711/how-to-pivot-rows-into-columns-mysql/164794