如何在 MySQL 中将动态列逆透视为行？

从动态列创建行

在数据管理领域，操作列和行通常是必要的。这个问题深入研究一个特定的任务：将多个动态列转换为一个新表，其中的行包含特定条件。

给定的示例说明了 Table_1 的逆透视，从而产生 Expected_Result_Table。主要区别在于 Expected_Result_Table 在单独的行中显示每个非零值。

MySQL 缺乏直接的 UNPIVOT 函数，但可以使用 UNION ALL 语句克服此限制。基本方法包括为每列创建单独的 SELECT 语句，选择 ID、列名称作为“word”值，以及列值作为“qty”值。然后使用 UNION ALL 组合这些 SELECT 语句。

例如：

SELECT id, 'abc' AS word, abc AS qty
FROM yt
WHERE abc > 0
UNION ALL
SELECT id, 'brt', brt
FROM yt
WHERE brt > 0

在处理动态列时，这种方法会变得更加复杂。为了解决这个问题，可以使用准备好的语句。它们允许基于元数据动态生成 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;

SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

在此示例中，动态 SQL 是使用来自information_schema.columns 表。然后使用准备好的语句 stmt 来执行动态生成的 SQL，有效地将动态列反转为行。

通过实现这种方法，可以将动态列转换为行，满足特定的数据需求。

以上是如何在 MySQL 中将动态列逆透视为行？的详细内容。更多信息请关注PHP中文网其他相关文章！