mysql - 将一个长表列变成多列表

Question

所以我有一个像这样的单列数据(大约 20 万行)的表

COL A
-----
A
B
C
D
E
F
G

...等等

我想把数据平均分布在几行的表格中

COL A   COL B   COL C   COL D
-----   -----   ------  ------
A       B       C       D
E       F       G       H
I       J       K       L

我一辈子都不知道该怎么做。任何帮助将不胜感激。

Answer 1

假设该表名为 mytable，它看起来像这样:

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>

试一试!!!