如何将 MySQL 列数据转换为行：一种 SQL 查询方法？

将 MySQL 列数据透视为行

使用 MySQL 时，常见的任务是将数据从列格式转换为行格式。这个过程称为数据透视，对于以更加用户友好且易于理解的方式呈现数据特别有用。

考虑以下场景：

您有三个表：

• 问题表（id、问题）
• 结果表（id、user_id、 job_id)
• 答案表（id、answer、fk_question_id、fk_result_id）

问题表包含可能问题的列表。结果表将用户与作业结果相关联。答案表存储用户对每个结果的问题的回答。

您的目标是将问题答案显示为每个结果集的列。期望的结果如下：

+-----------+---------+--------+-------------+----------+----------+
| 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       |
+-----------+---------+--------+-------------+----------+----------+

要实现此转换，您可以使用以下 MySQL 查询：

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

此查询结合使用 INNER JOIN 语句和 MAX( ) 和 CASE 函数来聚合每个问题的答案和结果组合。它有效地将数据从列格式转换为行格式，使您可以将答案可视化为列。

要执行此查询，请将 FROM 和 JOIN 子句中的表和列名称替换为您的实际表和列名称。执行后，查询将以所需的格式返回透视数据。

或者，如果您有大量问题，您可以使用查询的动态 SQL 版本来处理未知数量的问题。当透视结果中的列数未知或频繁变化时，这种动态方法特别有用。动态SQL版本如下：

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;

以上是如何将 MySQL 列数据转换为行：一种 SQL 查询方法？的详细内容。更多信息请关注PHP中文网其他相关文章！