UNPIVOT mysql 数据到其他表 [英] UNPIVOT mysql data to other table
本文介绍了UNPIVOT mysql 数据到其他表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下mysql数据
Hi i have the following mysql data
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
is there an easier way to do this using mysql/php
推荐答案
您需要UNPIVOT
您的数据.MySQL 没有内置函数来执行此操作,因此您需要使用多个查询.
You need to UNPIVOT
your data. MySQL doesn't have a built in function to do that so you'll need to use multiple queries.
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
,因此您需要处理这种情况.
ID
column here might cause issues. Depending on how you have defined it. If it is auto generated then you can remove it from the INSERT and let it be auto generated. Since you'll have rows for all months with same ID
, you need to handle that scenario.
这篇关于UNPIVOT mysql 数据到其他表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文