如何使用 MAX() 和 CASE 语句将 MySQL 列数据转换为行？

将 MySQL 数据透视表列数据显示为行

将表的列数据转换为行可能是一项具有挑战性的任务，尤其是对于多维数据集。在 MySQL 中，此操作通常称为“透视”。

问题：

提供的数据集由三个表组成：

• 问题：商店问题标题。
• 结果： 记录用户对特定职位 ID 的响应。
• 答案： 包含每个问题和结果组合的答案。

目标是重组数据以将问题答案显示为每个结果的列set.

解决方案：

要实现此目的，我们可以利用 MySQL 查询，该查询将 MAX() 聚合函数与 CASE 语句结合使用。下面的查询有效地将列数据转换为行：

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

解释：

• 查询从 Results (a) 表中选择各个字段，包括结果 ID、用户 ID 和作业 ID。
• 然后使用 INNER JOIN 从答案 (b) 表。
• 另一个 INNER JOIN 链接到问题 (c) 表，以识别每个答案的实际问题。
• MAX() 函数在 CASE 语句中应用聚合每个结果和问题组合的答案。
• 最后，结果按结果 ID、用户 ID 和作业 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

动态版本：

如果您有未知数量的问题（例如，1000），动态版本的查询更合适。这可以使用动态 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;

此动态查询使用变量串联根据 Question 表的内容动态构建 SQL 语句。结果是一个更灵活的查询，可以处理任意数量的问题。

以上是如何使用 MAX() 和 CASE 语句将 MySQL 列数据转换为行？的详细内容。更多信息请关注PHP中文网其他相关文章！