将一个长表列转换为具有多列的表 [英] Turn one long table column into table with multiple columns
本文介绍了将一个长表列转换为具有多列的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
所以我有一个带有单列数据(约20万行)的表，像这样
So I have a table with a single column of data (about 200k rows) like this
COL A
-----
A
B
C
D
E
F
G
...等
我想变成一张表，数据均匀地分布在几行上
That I want to turn into a table with that data distributed evenly across several rows
COL A COL B COL C COL D
----- ----- ------ ------
A B C D
E F G H
I J K L
我一生都不知道该怎么做.任何帮助将不胜感激.
I can't figure out for the life of me how to do this. Any help would be greatly appreciated.
推荐答案
假设表名为mytable，它看起来像这样:
Let's say the table is called mytable and it looks like this:
CREATE TABLE mytable
(
X VARCHAR(10)
);
让我们加载示例数据
mysql> drop database if exists user1267617;
Query OK, 3 rows affected (0.10 sec)
mysql> create database user1267617;
Query OK, 1 row affected (0.01 sec)
mysql> use user1267617
Database changed
mysql> CREATE TABLE mytable (X VARCHAR(10)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> insert into mytable values
-> ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),
-> ('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),
-> ('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z');
Query OK, 26 rows affected (0.06 sec)
Records: 26 Duplicates: 0 Warnings: 0
mysql> select * from mytable;
+------+
| X |
+------+
| A |
| B |
| C |
| D |
| E |
| F |
| G |
| H |
| I |
| J |
| K |
| L |
| M |
| N |
| O |
| P |
| Q |
| R |
| S |
| T |
| U |
| V |
| W |
| X |
| Y |
| Z |
+------+
26 rows in set (0.00 sec)
mysql>
这是您需要的代码:
CREATE TABLE mytmp
(
id int not null auto_increment,
X varchar(10),
groupnum int,
groupndx int,
primary key (id)
);
INSERT INTO mytmp (X) select X from mytable;
UPDATE mytmp SET groupndx = MOD(id - 1,4),groupnum = FLOOR((id - 1)/4);
alter table mytmp add index (groupnum);
select * from mytmp;
CREATE TABLE mynewtable
(
id int not null auto_increment,
groupnum int,
colA varchar(10) default '',
colB varchar(10) default '',
colC varchar(10) default '',
colD varchar(10) default '',
key (groupnum),
primary key (id)
);
insert into mynewtable (colA,groupnum)
select X,groupnum from mytmp where groupndx = 0;
update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
AND B.groupndx=1 set A.colB = B.x;
update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
AND B.groupndx=2 set A.colC = B.x;
update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
AND B.groupndx=3 set A.colD = B.x;
alter table mynewtable drop column groupnum;
select * from mynewtable;
这是结果:
mysql> CREATE TABLE mytmp
-> (
-> id int not null auto_increment,
-> X varchar(10),
-> groupnum int,
-> groupndx int,
-> primary key (id)
-> );
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT INTO mytmp (X) select X from mytable;
Query OK, 26 rows affected (0.07 sec)
Records: 26 Duplicates: 0 Warnings: 0
mysql> UPDATE mytmp SET groupndx = MOD(id - 1,4),groupnum = FLOOR((id - 1)/4);
Query OK, 26 rows affected (0.06 sec)
Rows matched: 26 Changed: 26 Warnings: 0
mysql> alter table mytmp add index (groupnum);
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from mytmp;
+----+------+----------+----------+
| id | X | groupnum | groupndx |
+----+------+----------+----------+
| 1 | A | 0 | 0 |
| 2 | B | 0 | 1 |
| 3 | C | 0 | 2 |
| 4 | D | 0 | 3 |
| 5 | E | 1 | 0 |
| 6 | F | 1 | 1 |
| 7 | G | 1 | 2 |
| 8 | H | 1 | 3 |
| 9 | I | 2 | 0 |
| 10 | J | 2 | 1 |
| 11 | K | 2 | 2 |
| 12 | L | 2 | 3 |
| 13 | M | 3 | 0 |
| 14 | N | 3 | 1 |
| 15 | O | 3 | 2 |
| 16 | P | 3 | 3 |
| 17 | Q | 4 | 0 |
| 18 | R | 4 | 1 |
| 19 | S | 4 | 2 |
| 20 | T | 4 | 3 |
| 21 | U | 5 | 0 |
| 22 | V | 5 | 1 |
| 23 | W | 5 | 2 |
| 24 | X | 5 | 3 |
| 25 | Y | 6 | 0 |
| 26 | Z | 6 | 1 |
+----+------+----------+----------+
26 rows in set (0.00 sec)
mysql> CREATE TABLE mynewtable
-> (
-> id int not null auto_increment,
-> groupnum int,
-> colA varchar(10) default '',
-> colB varchar(10) default '',
-> colC varchar(10) default '',
-> colD varchar(10) default '',
-> key (groupnum),
-> primary key (id)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> insert into mynewtable (colA,groupnum)
-> select X,groupnum from mytmp where groupndx = 0;
Query OK, 7 rows affected (0.07 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
-> AND B.groupndx=1 set A.colB = B.x;
AND B.groupndx=3 set A.colD = B.x;
Query OK, 7 rows affected (0.07 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
-> AND B.groupndx=2 set A.colC = B.x;
Query OK, 6 rows affected (0.06 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
-> AND B.groupndx=3 set A.colD = B.x;
Query OK, 6 rows affected (0.06 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> alter table mynewtable drop column groupnum;
Query OK, 7 rows affected (0.26 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from mynewtable;
+----+------+------+------+------+
| id | colA | colB | colC | colD |
+----+------+------+------+------+
| 1 | A | B | C | D |
| 2 | E | F | G | H |
| 3 | I | J | K | L |
| 4 | M | N | O | P |
| 5 | Q | R | S | T |
| 6 | U | V | W | X |
| 7 | Y | Z | | |
+----+------+------+------+------+
7 rows in set (0.00 sec)
mysql>
尝试一下！
这篇关于将一个长表列转换为具有多列的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
