主页 / dba / 问题 / 198431
Accepted
roffster
Asked: 2018-02-21 19:52:12 +0800 CST

将 50 多个列表转换为 1 个小得多的表

  • 772

我目前正在处理一个遗留的 PLC 设备，它在一个烦人的 MYSQL 表中吐出数据。表结构为：

PK | FK | FK | FK | Result1 | Result2 | ... | ResultN

可以想象，对此执行任何类型的查询或连接都非常麻烦，并且需要很长时间才能解决。

我想设置一个自动 select -> insert 语句来提取数据并将其放入更合适的表中

PK | FK | FK | FK | Result Number | Result Value

不幸的是，我一直在研究如何提取列名，以便我知道用什么填充Result Number列，以及如何跨表交叉乘法以确保每个新行都具有每个值所需的所有键。

mysql unpivot

4 个回答

  1. 您需要 NINSERT ... SELECT ...个语句。一次性繁琐的任务。

    • 1

  2. 如果需要在磁盘上连续存储具有相同 FK 集的行（这不是不合理的），您可能更愿意在单个语句中执行转换，按 FK 对所有行进行排序。

    您可以使用数字表来做到这一点：

    INSERT INTO
  YourResultTable (FK1, FK2, FK3, ResultNumber, ResultValue)
SELECT
  t.FK1,
  t.FK2,
  t.FK3,
  n.Number AS ResultNumber,
  CASE n.Number
    WHEN 1 THEN t.Result1
    WHEN 2 THEN t.Result2
    ...
    WHEN N THEN t.ResultN
  END AS ResultValue
FROM
  YourTable AS t
  CROSS JOIN Numbers AS n
WHERE
  n.Number BETWEEN 1 AND the_N_value
ORDER BY
  t.FK1    ASC,
  t.FK2    ASC,
  t.FK3    ASC,
  n.Number ASC
;

    如果源表太大而无法一次性转换，您可以一次分批处理几个（数百？数千？）FK 集，确保每组具有相同 FK 的行被不分开地插入。例如 FK1 上的过滤器可以实现这一点，所以第一次是

    ...
WHERE
  n.Number BETWEEN 1 AND the_N_value
  AND t.FK1 <= some_value
...

    那么下次

    ...
WHERE
  n.Number BETWEEN 1 AND the_N_value
  AND t.FK1 > some_value AND t.FK1 <= some_other_value
...

    等等。这可能比Rick James 的建议更乏味，因为您可能并不总是知道在多少批次中进行这种拆分以及使用什么值进行过滤。但是如果连续存储相同的 FK 是一个要求，这是满足它的一种方法。

    • 1

  3. 您需要使用一个INSERT ... SELECT语句，利用 2 个（不广为人知的）MySQL 函数：

    • COALESCE()- 给定传递的值列表（您的列），返回第一个非NULL值。
    • FIELD()- 第一个参数是要查找的值，结果是它在后续参数列表中的位置。

    我无法为您编写您的查询，但它应该大致如下所示：

    INSERT INTO new_table (val, num)
    SELECT
    @v := COALESCE(col1, col2, col3...),
    FIELD(@v, col1, col2, col3...)
    FROM old_table;

    我使用用户变量( @v) 只是为了提高可读性，以避免重复整个COALESCE().

    请注意，我的示例要求列NULL在未设置任何值时。如果您改用其他内容（例如，一个空字符串），则需要为每一列指定它：

    NULLIF(col1, '')
    • 0
  4. Best Answer

    我通过使用https://stackoverflow.com/questions/16359345/transposing-dynamic-columns-to-rows中的代码找到了一个解决方案，该代码在 5 年后仍然有效。基本上，我使用了动态 SQL 语句：

    SET @sql = NULL;

SET SESSION group_concat_max_len = 1000000;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select col1, col2, ''',
      c.column_name,
      ''' as Measurement, ',
      c.column_name,
      ' as Result 
      from housings
      where col1 > 0'

    ) SEPARATOR ' UNION ALL '
  )  into @sql
FROM information_schema.columns c
where c.table_name = 'housings'
  and c.column_name not in ('col1', col2)
order by c.ordinal_position;



SET @sql 
  = CONCAT('Insert into unpivoted_parts_table(col1, col2, Measurement, Result)
            select col1, col2, Measurement, Result
           from
           (', @sql, ') x  order by id');



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

相关问题