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

相关文章