MySQL 组合表并将具有唯一行 ID 的多个列转置为非唯一行

Question

我正在尝试将多个表合并为一个表，并通过将表从列转换为行来更改数据集的结构。我正在使用 MySQL。

我已经尝试了几天来找到一个智能解决方案，而无需手动将具有唯一 ID 的每一行转换为具有相同 ID 的多行。但是，我发现自己卡住了。

我当前的数据集:

表一:

ID    M1 amount    M2 amount    M3 amount
1234  $400 543,00  $600 000,00  $500 321,00 
4566  $500 321,00  $300 012,00  $200 345,00 
3311  $300 000,00  $450 000,00  $100 312,00 

表二:

ID    M1 units  M2 units  M3 units
1234  30811 pc  46154 pc  38486 pc
4566  38486 pc  23078 pc  15411 pc
3311  23077 pc  34615 pc  7716 pc

我想要实现的是一个具有以下结构的表:

ID    M    Units    Amounts
1234  1    30811 pc  $400 543,00 
1234  2    46154 pc  $600 000,00 
1234  3    38486 pc  $500 321,00 
4566  1    38486 pc  $500 321,00 
4566  2    23078 pc  $300 012,00 
4566  3    15411 pc  $200 345,00 
3311  1    23077 pc  $300 000,00 
3311  2    34615 pc  $450 000,00 
3311  3    7716 pc   $100 312,00

非常感谢您对这个问题的帮助。

Answer 1

下面的 sql 脚本可以完成这项工作。使用 UNION ALL，我们将 Mx units 和 Mx amount 数据转换为行，并将它们保存在临时表。然后我们将它们加入 ID 和 M 列。

CREATE TEMPORARY TABLE tmp_t1 AS (
    SELECT *
    FROM (
        SELECT ID, `M1 amount` AS Amount, '1' AS M FROM t1
        UNION ALL
        (SELECT ID, `M2 amount` AS Amount, '2' AS M FROM t1)
        UNION ALL
        (SELECT ID, `M3 amount` AS Amount, '3' AS M FROM t1)
    ) AS x
    ORDER BY ID ASC
);

CREATE TEMPORARY TABLE tmp_t2 AS (
    SELECT *
    FROM (
        SELECT ID, `M1 units` AS Units, '1' AS M FROM t2
        UNION ALL
        (SELECT ID, `M2 units` AS Units, '2' AS M FROM t2)
        UNION ALL
        (SELECT ID, `M3 units` AS Units, '3' AS M FROM t2)
    ) AS x
    ORDER BY ID ASC
);

SELECT
    t1.ID,
    t1.M,
    t2.Units,
    t1.Amount
FROM
    tmp_t1 AS t1
INNER JOIN tmp_t2 AS t2 ON t1.ID = t2.ID AND t1.M = t2.M;

输出

+------+---+----------+-------------+
| ID   | M | Units    | Amount      |
+------+---+----------+-------------+
| 1234 | 1 | 30811 pc | $400 543,00 |
| 1234 | 2 | 46154 pc | $600 000,00 |
| 1234 | 3 | 38486 pc | $500 321,00 |
| 3311 | 1 | 23077 pc | $300 000,00 |
| 3311 | 2 | 34615 pc | $450 000,00 |
| 3311 | 3 | 7716 pc  | $100 312,00 |
| 4566 | 1 | 38486 pc | $500 321,00 |
| 4566 | 2 | 23078 pc | $300 012,00 |
| 4566 | 3 | 15411 pc | $200 345,00 |
+------+---+----------+-------------+

示例数据

DROP DATABASE IF EXISTS so_example;
CREATE DATABASE so_example;
USE so_example;

CREATE TABLE t1 (
    ID INT UNSIGNED NOT NULL PRIMARY KEY,
    `M1 amount` VARCHAR(255),
    `M2 amount` VARCHAR(255),
    `M3 amount` VARCHAR(255)
);

INSERT INTO t1 (ID, `M1 amount`, `M2 amount`, `M3 amount`) VALUES
('1234', '$400 543,00', '$600 000,00', '$500 321,00'),
('4566', '$500 321,00', '$300 012,00', '$200 345,00'),
('3311', '$300 000,00', '$450 000,00', '$100 312,00');

CREATE TABLE t2 (
    ID INT UNSIGNED NOT NULL PRIMARY KEY,
    `M1 units` VARCHAR(255),
    `M2 units` VARCHAR(255),
    `M3 units` VARCHAR(255)
);

INSERT INTO t2 (ID, `M1 units`, `M2 units`, `M3 units`) VALUES
('1234', '30811 pc', '46154 pc', '38486 pc'),
('4566', '38486 pc', '23078 pc', '15411 pc'),
('3311', '23077 pc', '34615 pc', '7716 pc');