MySQL SQL 拆分逗号分隔行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19073500/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL split comma separated row
提问by Don P
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问题都试图做更复杂的事情）
回答by peterm
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(numbers) 表和在这种情况下非常方便的 MySQL 函数SUBSTRING_INDEX()。如果您进行了大量此类查询（拆分），那么您可能会考虑填充和使用持久化的计数表，而不是像本例中那样使用子查询即时生成它。此示例中的子查询生成从 1 到 100 的数字序列，有效地允许您在源表中的每行拆分多达 100 个分隔值。如果您需要更多或更少，您可以轻松调整它。
Output:
输出：
| VALUE | |----------------| | somethingA | | somethingB | | somethingC | | somethingElseA | | somethingElseB |
Here is SQLFiddledemo
这是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 SQLFiddledemo
这是SQLFiddle演示
