SQL拆分逗号分隔行[复制]

This question already has an answer here:

这个问题在这里已有答案：

• MySQL: Split comma separated list into multiple rows 4 answers
• MySQL：将逗号分隔列表分成多行4个答案

I have a column with a variable number of comma seperated values:

我有一个包含可变数量的逗号分隔值的列：

somethingA,somethingB,somethingC
somethingElseA, somethingElseB

And I want the result to take each value, and create a row:

我希望结果取每个值，并创建一行：

somethingA
somethingB
somethingC
somethingElseA
somethingElseB

How can I do this in SQL (MySQL)?

我怎么能在SQL（MySQL）中这样做？

(I've tried googling "implode" and "lateral view", but those don't seem to turn up related questions. All the related SO questions are trying to do much more complicated things)

（我已经尝试使用谷歌搜索“内爆”和“横向视图”，但那些似乎没有出现相关的问题。所有相关的SO问题都试图做更复杂的事情）

1 个解决方案

#1

You can do it with pure SQL like this

您可以像这样使用纯SQL

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
  FROM table1 t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))
 ORDER BY value

Note: The trick is to leverage tally(numbers) table and a very handy in this case MySQL function SUBSTRING_INDEX(). If you do a lot of such queries (splitting) then you might consider to populate and use a persisted tally table instead of generating it on fly with a subquery like in this example. The subquery in this example generates a sequence of numbers from 1 to 100 effectively allowing you split up to 100 delimited values per row in source table. If you need more or less you can easily adjust it.

注意：诀窍是利用tally（数字）表，在这种情况下MySQL函数SUBSTRING_INDEX（）非常方便。如果你做了很多这样的查询（拆分），那么你可能会考虑填充并使用一个持久的计数表，而不是像本例中那样使用子查询生成它。此示例中的子查询生成一个从1到100的数字序列，有效地允许您在源表中每行最多分割100个分隔值。如果您需要更多或更少，您可以轻松调整它。

Output:

输出：

|          VALUE |
|----------------|
|     somethingA |
|     somethingB |
|     somethingC |
| somethingElseA |
| somethingElseB |

Here is SQLFiddle demo

这是SQLFiddle演示

---

This is how the query might look with a persisted tally table

这是查询在持久性计数表中的外观

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
  FROM table1 t CROSS JOIN tally n
 WHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))
 ORDER BY value

Here is SQLFiddle demo

这是SQLFiddle演示