更新时间:2023-12-01 15:22:16
使用给定的示例数据,您可以执行以下操作-
With the given example data you can do something as below -
mysql> create table test (marks varchar(100));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into test values ('Maths-80,Phy-100,Che-99'),('Maths-90,Phy-60'),('Phy-82,Che-65'),('Che-90'),('Maths-33,Phy-89,Che-65'),('Maths-50,Phy-43,Che-59');
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from test ;
+-------------------------+
| marks |
+-------------------------+
| Maths-80,Phy-100,Che-99 |
| Maths-90,Phy-60 |
| Phy-82,Che-65 |
| Che-90 |
| Maths-33,Phy-89,Che-65 |
| Maths-50,Phy-43,Che-59 |
+-------------------------+
6 rows in set (0.00 sec)
现在使用locate
和substring_index
,我们可以将值提取为
Now using locate
and substring_index
we can extract the values as
select marks,
case
when locate('Maths',marks) > 0 then substring_index(substring_index(marks,'Maths-',-1),',',1) else 0 end
as Maths ,
case
when locate('Phy',marks) > 0 then substring_index(substring_index(marks,'Phy-',-1),',',1) else 0 end
as Phy ,
case
when locate('Che',marks) > 0 then substring_index(substring_index(marks,'Che-',-1),',',1) else 0 end
as Che
from test ;
输出:
+-------------------------+-------+------+------+
| marks | Maths | Phy | Che |
+-------------------------+-------+------+------+
| Maths-80,Phy-100,Che-99 | 80 | 100 | 99 |
| Maths-90,Phy-60 | 90 | 60 | 0 |
| Phy-82,Che-65 | 0 | 82 | 65 |
| Che-90 | 0 | 0 | 90 |
| Maths-33,Phy-89,Che-65 | 33 | 89 | 65 |
| Maths-50,Phy-43,Che-59 | 50 | 43 | 59 |
+-------------------------+-------+------+------+
6 rows in set (0.00 sec)