mysql - 在 mySQL 中从一行生成多行

Question

我找不到在 mySQL 中执行此操作的任何解决方案。有什么功能吗？

我尝试用 SUBSTRING_INDEX 来解决这个问题，但我无法用它解决。

我的表格是这样的:

+----+-------+--------------+
| id | group | work_place   |
+----+-------+--------------+
| 1  |  TTL  | ERG PT TT RK |
+----+----------------------+
| 2  |  PFF  | ER PT TL KK  |
+----+-------+--------------+

我需要这个:

+-------+------------+------+
| group | work_place | No.  |
+-------+------------+------+
|  TTL  |     ERP    | 1    |
+-------+------------+------+
|  TTL  |     PT     | 2    |
+-------+------------+------+
|  TTL  |     TT     | 3    |
+-------+------------+------+
|  TTL  |     RK     | 4    |
+-------+------------+------+
|  PFF  |     ER     | 1    |
+-------+------------+------+
|  PFF  |     PT     | 2    |
+-------+------------+------+
|  PFF  |     TL     | 3    |
+-------+------------+------+
|  PFF  |     KK     | 4    |
+-------+------------+------+

Answer 1

这个怎么样(这只是一个想法，可能有人可以用 sql 函数改进它)，

insert into your_table_name (group,work_place) 
SELECT group,SUBSTRING_INDEX(work_place, ' ', 1)  FROM  your_table_name;
insert into your_table_name (group,work_place) 
SELECT group,SUBSTRING_INDEX(SUBSTRING_INDEX(work_place,' ', 2), ' ',-1) FROM  your_table_name;
insert into your_table_name (group,work_place) 
SELECT group,SUBSTRING_INDEX(SUBSTRING_INDEX(work_place,' ', 3), ' ',-1) FROM  your_table_name;
insert into your_table_name (group,work_place) 
SELECT group,SUBSTRING_INDEX(work_place, ' ', -1) FROM  your_table_name;