我找不到在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    |
+-------+------------+------+

最佳答案

怎么样(这只是一个主意,也许有人可以使用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;

关于mysql - 在MySQL中从一个开始制作多行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/55689420/

10-10 18:29
查看更多