mysql - 在 MySQL 上将值拆分为多行

Question

这是我在 mysql 表中的数据:

+---------+-------------------+------------+
| ID      | Name              | Class      |
+---------+-------------------+------------+
| 1, 2, 3 | Alex, Brow, Chris | Aa, Bb, Cc |
+---------+-------------------+------------+

我想将值拆分为多行以获取以下格式的数据。

1     Alex     Aa
2     Brow     Bb
3     Chris    Cc

我该怎么做？

Answer 1

一个技巧是用数字加入 Tally 表。
然后使用 SUBSTRING_INDEX 获取零件。

如果您还没有数字表，这里有一种方法。

drop table if exists Digits;
create table Digits (n int primary key not null);
insert into Digits (n) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

drop table if exists Nums;
create table Nums (n int primary key not null);

insert into Nums (n) 
select (n3.n*100+n2.n*10+n1.n) as n
from Digits n1
cross join Digits n2
cross join Digits n3;

然后它可以用来展开那些列

示例数据:

drop table if exists YourTable;
create table YourTable (
  ID varchar(30) not null,
  Name varchar(30) not null,
  Class varchar(30) not null
);

insert into YourTable 
  (ID, Name, Class) values
  ('1, 2, 3', 'Alex, Brow, Chris', 'Aa, Bb, Cc')
, ('4, 5, 6', 'Drake, Evy, Fiona', 'Dd, Ee, Ff')
;

查询:

SELECT 
 LTRIM(SUBSTRING_INDEX( SUBSTRING_INDEX( t.ID, ',', Nums.n), ',', -1)) AS Id,
 LTRIM(SUBSTRING_INDEX( SUBSTRING_INDEX( t.Name, ',', Nums.n), ',', -1)) AS Name,
 LTRIM(SUBSTRING_INDEX( SUBSTRING_INDEX( t.Class, ',', Nums.n), ',', -1)) AS Class
FROM YourTable t
LEFT JOIN Nums ON n BETWEEN 1 AND (LENGTH(ID)-LENGTH(REPLACE(ID, ',', ''))+1);

结果:

Id  Name    Class
1   Alex    Aa
2   Brow    Bb
3   Chris   Cc
4   Drake   Dd
5   Evy     Ee
6   Fiona   Ff