MySQL объединяет таблицы и транспонирует несколько столбцов с уникальным идентификатором строки в неуникальные строки

Question

Я пытаюсь объединить несколько таблиц в одну и изменить структуру набора данных путем транспонирования таблиц из столбцов в строки. Я использую MySQL.

Я уже пару дней пытался найти умное решение, не перенося вручную каждую строку с уникальным идентификатором в несколько строк с одинаковым идентификатором. Тем не менее, я застрял.

Мой текущий набор данных:

Таблица первая:

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');