我正在努力寻找解决此 MySQL 问题的方法.我似乎无法理解如何去做.我有以下表格.
I'm struggling to find a solution this MySQL problem. I just can't seem to get my head around how to do it. I have the following tables.
Question table
+----+-------------+
| id | question |
+----+-------------+
| 1 | Is it this? |
| 2 | Or this? |
| 3 | Or that? |
+----+-------------+
Results Table
+----+---------+--------+
| id | user_id | job_id |
+----+---------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 2 | 3 |
+----+---------+--------+
Answers table
+----+-------------------------+--------------+
| id | answer | fk_question_id | fk_result_id |
+----+-------------------------+--------------+
| 1 | Yes | 1 | 1 |
| 2 | No | 2 | 1 |
| 3 | Maybe | 3 | 1 |
| 4 | Maybe | 1 | 2 |
| 5 | No | 2 | 2 |
| 6 | Maybe | 3 | 2 |
| 7 | Yes | 1 | 3 |
| 8 | Yes | 2 | 3 |
| 9 | No | 3 | 3 |
+----+-------------------------+--------------+
如果可能,我想将问题答案显示为每个结果集的列,就像这样.
If possible I'd like to display the question answers as columns for each result set, like this.
+-----------+---------+--------+-------------+----------+----------+
| result_id | user_id | job_id | Is it this? | Or this? | Or that? |
+-----------+---------+--------+-------------+----------+----------+
| 1 | 1 | 1 | Yes | No | Maybe |
| 2 | 1 | 3 | Maybe | No | Maybe |
| 3 | 2 | 3 | Yes | Yes | No |
+-----------+---------+--------+-------------+----------+----------+
任何帮助将不胜感激.
谢谢
SELECT a.ID,
a.user_ID,
a.job_id,
MAX(CASE WHEN c.question = 'Is it this?' THEN b.answer END) 'Is it this?',
MAX(CASE WHEN c.question = 'Or this?' THEN b.answer END) 'Or this?',
MAX(CASE WHEN c.question = 'Or that? ' THEN b.answer END) 'Or that? '
FROM Results a
INNER JOIN Answers b
ON a.id = b.fk_result_id
INNER JOIN Question c
ON b.fk_question_id = c.ID
GROUP BY a.ID,
a.user_ID,
a.job_id
如果您有未知数量的问题(特别是像 Matei Mihai 所说的 1000 个),则非常需要动态版本.
If you have unknow number of questions (specifically 1000 like Matei Mihai said), a dynamic version is much required.
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(CASE WHEN c.question = ''',
question,
''' then b.answer end) AS ',
CONCAT('`',question,'`')
)
) INTO @sql
FROM Question;
SET @sql = CONCAT('SELECT a.ID,
a.user_ID,
a.job_id, ', @sql, '
FROM Results a
INNER JOIN Answers b
ON a.id = b.fk_result_id
INNER JOIN Question c
ON b.fk_question_id = c.ID
GROUP BY a.ID,
a.user_ID,
a.job_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
输出
╔════╦═════════╦════════╦═════════════╦══════════╦══════════╗
║ ID ║ USER_ID ║ JOB_ID ║ IS IT THIS? ║ OR THIS? ║ OR THAT? ║
╠════╬═════════╬════════╬═════════════╬══════════╬══════════╣
║ 1 ║ 1 ║ 1 ║ Yes ║ No ║ Maybe ║
║ 2 ║ 1 ║ 3 ║ Maybe ║ No ║ Maybe ║
║ 3 ║ 2 ║ 3 ║ Yes ║ Yes ║ No ║
╚════╩═════════╩════════╩═════════════╩══════════╩══════════╝
这篇关于MySQL 数据透视表列数据作为行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!