mysql - 如何将值拆分为多行(MySQL)

Question

这个问题已经有答案了:

SQL split values to multiple rows (12 个回答)

已关闭 去年。

我想在 SQL 中将一行拆分为多行。

例如，我有'A ADCT DROP INCALL -'，其中包含空格和“-”，并且我希望这些值在多行中保持独立.

原始结果:

SELECT statuses FROM campaigns;
+-----------------------------------------+
| RESULT OF QUERY                         |
+-----------------------------------------+
| A ADCT DROP INCALL -                    |
+-----------------------------------------+

结果必须喜欢:

+-----------------------------------------+
| RESULT OF QUERY                         |
+-----------------------------------------+
| A                                       |
| ADCT                                    |
| DROP                                    |
| INCALL                                  |
+-----------------------------------------+

我尝试使用 SUBSTRING_INDEX ，例如:

SELECT status
FROM statuses
WHERE statuses.status NOT IN (SELECT SUBSTRING_INDEX(TRIM(dial_statuses)," ", 1) FROM campaigns WHERE campaign_id = '4000')
ORDER BY STATUS;

抱歉，我知道这是很基本的。

Answer 1

我相信你可以这样做:

SELECT
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 
  name
FROM
  numbers 
INNER JOIN tablename ON CHAR_LENGTH(tablename.name)
 -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
ORDER BY
  id, n

请参阅 fiddle here .

如果无法创建表，则解决方案可以是:

SELECT
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 
  name
FROM
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers 
INNER JOIN tablename ON CHAR_LENGTH(tablename.name)
 -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
ORDER BY
  id, n

fiddle 的例子是 here .