MySQL 数据透视表列数据作为行

2021-11-20 00:00:00 pivot sql mysql

我正在努力寻找解决此 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

  • SQLFiddle 演示

    • 如果您有未知数量的问题(特别是像 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;

    • SQLFiddle 演示

      • 输出

      ╔════╦═════════╦════════╦═════════════╦══════════╦══════════╗
║ 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       ║
╚════╩═════════╩════════╩═════════════╩══════════╩══════════╝

相关文章