mysql - 如何在mysql中将一列拆分为多列

我有这样的专栏

+--------------------------+
| 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    |
+--------------------------+

我只给出了3个示例主题，但在realdata中，它可能是由逗号分隔的任意数量的主题。为此，我想通过拆分标记列来显示每个主题的标记，并像下面的示例一样在多列中显示它。

 +-------------------------------------+-------------+------------+| 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 | 90 | 0 | 0 |Maths-33,Phy-89,Che-65 | 33 | 89 | 65 |Maths-50,Phy-43,Che-59 | 50 | 43 | 59 |+-------------------------------------+-------------+-------------+

最佳答案

使用给定的示例数据，您可以执行以下操作-

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我们可以将值提取为

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)