<tfoot id='NKffX'></tfoot>
    1. <small id='NKffX'></small><noframes id='NKffX'>

      <legend id='NKffX'><style id='NKffX'><dir id='NKffX'><q id='NKffX'></q></dir></style></legend>
      <i id='NKffX'><tr id='NKffX'><dt id='NKffX'><q id='NKffX'><span id='NKffX'><b id='NKffX'><form id='NKffX'><ins id='NKffX'></ins><ul id='NKffX'></ul><sub id='NKffX'></sub></form><legend id='NKffX'></legend><bdo id='NKffX'><pre id='NKffX'><center id='NKffX'></center></pre></bdo></b><th id='NKffX'></th></span></q></dt></tr></i><div id='NKffX'><tfoot id='NKffX'></tfoot><dl id='NKffX'><fieldset id='NKffX'></fieldset></dl></div>

        • <bdo id='NKffX'></bdo><ul id='NKffX'></ul>

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

        时间:2023-06-01

          <tfoot id='V06tj'></tfoot>

            • <i id='V06tj'><tr id='V06tj'><dt id='V06tj'><q id='V06tj'><span id='V06tj'><b id='V06tj'><form id='V06tj'><ins id='V06tj'></ins><ul id='V06tj'></ul><sub id='V06tj'></sub></form><legend id='V06tj'></legend><bdo id='V06tj'><pre id='V06tj'><center id='V06tj'></center></pre></bdo></b><th id='V06tj'></th></span></q></dt></tr></i><div id='V06tj'><tfoot id='V06tj'></tfoot><dl id='V06tj'><fieldset id='V06tj'></fieldset></dl></div>
            • <small id='V06tj'></small><noframes id='V06tj'>

                <legend id='V06tj'><style id='V06tj'><dir id='V06tj'><q id='V06tj'></q></dir></style></legend>

                  <bdo id='V06tj'></bdo><ul id='V06tj'></ul>
                    <tbody id='V06tj'></tbody>
                  本文介绍了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       ║
                      ╚════╩═════════╩════════╩═════════════╩══════════╩══════════╝
                      

                      这篇关于MySQL 数据透视表列数据作为行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:MySQL动态交叉表 下一篇:如何填补 MySQL 中的日期空白?

                  相关文章

                    • <bdo id='1fPMR'></bdo><ul id='1fPMR'></ul>
                  1. <small id='1fPMR'></small><noframes id='1fPMR'>

                      <tfoot id='1fPMR'></tfoot>
                    1. <i id='1fPMR'><tr id='1fPMR'><dt id='1fPMR'><q id='1fPMR'><span id='1fPMR'><b id='1fPMR'><form id='1fPMR'><ins id='1fPMR'></ins><ul id='1fPMR'></ul><sub id='1fPMR'></sub></form><legend id='1fPMR'></legend><bdo id='1fPMR'><pre id='1fPMR'><center id='1fPMR'></center></pre></bdo></b><th id='1fPMR'></th></span></q></dt></tr></i><div id='1fPMR'><tfoot id='1fPMR'></tfoot><dl id='1fPMR'><fieldset id='1fPMR'></fieldset></dl></div>
                      <legend id='1fPMR'><style id='1fPMR'><dir id='1fPMR'><q id='1fPMR'></q></dir></style></legend>