php - UNPIVOT mysql数据到其他表

Question

您好，我有以下 mysql 数据

INSERT INTO `monthly` (`id`, `year`, `stat_id`, `cat_id`, `January`, `February`, `March`, `April`, `May`, `June`, `July`, `August`, `September`, `October`, `November`, `December`) VALUES
(1, '2017', '12', '25', '1', '3', '1', '1', '3', '4', '4', '2', '4', '', '', ''),

我希望它能变成这样

INSERT INTO `monthlydata` (`id`, `year`, `monthName`, `stat_id`, `cat_id`, `data`) VALUES
(1, '2017', 'January', '12', '25', '1'),
(2, '2017', 'February', '12', '25', '3'),
(3, '2017', 'March', '12', '25', '1'),
(4, '2017', 'April', '12', '25', '1'),
(5, '2017', 'May', '12', '25', '3'),
(6, '2017', 'June', '12', '25', '4'),
(7, '2017', 'July', '12', '25', '4'),
(8, '2017', 'August', '12', '25', '2'),
(9, '2017', 'September', '12', '25', '4'),
(10, '2017', 'October', '12', '25', ''),
(11, '2017', 'November', '12', '25', ''),
(12, '2017', 'December', '12', '25', ''),

有没有更简单的方法使用 mysql/php 来做到这一点

Answer 1

您需要UNPIVOT 您的数据。 MySQL 没有内置函数来执行此操作，因此您需要使用多个查询。

INSERT INTO `monthlydata` (`id`, `year`, `monthName`, `stat_id`, `cat_id`, `data`) VALUES
SELECT id, year, 'January', stat_id, cat_id, January
    FROM monthly WHERE monthName = 'January'
UNION ALL 
SELECT id, year, 'February', stat_id, cat_id, February
    FROM monthly WHERE monthName = 'February'
UNION ALL 
SELECT id, year, 'March', stat_id, cat_id, March
    FROM monthly WHERE monthName = 'March'
.....

ID 列可能会导致问题。取决于您如何定义它。如果它是自动生成的，那么您可以将其从 INSERT 中删除并让它自动生成。由于所有月份的行都具有相同的 ID，因此您需要处理这种情况。