当您缺乏 UNPIVOT 功能时，如何在 MySQL 中使用动态列逆透视数据？

使用动态列逆透视数据

在查询转换领域，逆透视数据在重塑表结构中起着至关重要的作用。其中一个场景涉及将动态列转置为行，MySQL 缺乏 UNPIVOT 函数可能会阻碍这项任务。

问题：转置动态列

想象一个名为 Table_1 的表具有代表不同属性的动态列（abc、brt、ccc等），如图所示下面：

Table_1
-----------------------------------------
Id       abc  brt ccc ddq eee fff gga hxx
-----------------------------------------
12345     0    1   0   5   0   2   0   0  
21321     0    0   0   0   0   0   0   0   
33333     2    0   0   0   0   0   0   0   
41414     0    0   0   0   5   0   0   1   
55001     0    0   0   0   0   0   0   2   
60000     0    0   0   0   0   0   0   0 
77777     9    0   3   0   0   0   0   0

目标是将 Table_1 转换为 Expected_Result_Table，将列列为行，仅包含非零值：

Expected_Result_Table
---------------------
Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

解决方案：动态 UNION ALL 和准备好的语句

为了完成这种转换，我们可以利用 UNION 的组合ALL 和处理动态列的预准备语句：

1. 生成动态 SQL： 使用 information_schema.columns，我们可以生成为每个非零列选择行的动态 SQL。
2. 准备语句：我们使用准备好的语句来执行动态SQL并避免潜在的SQL注入
3. 执行语句：我们执行准备好的语句来检索未透视的数据。

这是动态解决方案的代码：

-- Generate dynamic SQL
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name = 'yt'
  and c.column_name not in ('id')
order by c.ordinal_position;

-- Build the final SQL query
SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');

-- Prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

结果：

结果表将包含所需的行：

Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

以上是当您缺乏 UNPIVOT 功能时，如何在 MySQL 中使用动态列逆透视数据？的详细内容。更多信息请关注PHP中文网其他相关文章！