SQL拆分逗号分隔的行 [英] SQL split comma separated row

问题描述

  somethingA，somethingB，somethingC 
 somethingElseA ，somethingElseB 
  

我想让结果取每个值并创建一行：

  somethingA 
 somethingB 
 somethingC 
 somethingElseA 
 somethingElseB 
   b 
 $ b 
（我试过用google搜索内爆和横向视图，但这些似乎并没有提出相关的问题。做更复杂的事情）
解决方案
您可以使用像这样的纯SQL来做到这一点
  SELECT SUBSTRING_INDEX（SUBSTRING_INDEX（t.values，'，'，nn），'，'，-1）value 
 FROM table1 t CROSS JOIN 
（
 SELECT aN + bN * 10 + 1 n 
 FROM 
（选择0作为所有选择1所有选择2所有选择3所有选择4所有选择5所有选择5所有ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9）a 
，（选择0作为所有选择1所有选择2所有选择3所有选择所有选择4所有选择5所有选择所有选择6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9）b 
 ORDER BY n 
）n 
 where nn <= 1 +（LENGTH（t.values） -  LENGTH（REPLACE （t.values，'，'， '）））
 ORDER BY值
  
  注意： strong>诀窍在于利用计数（数字）表，并且在这种情况下非常方便MySQL函数  SUBSTRING_INDEX（） 。如果你做了很多这样的查询（拆分），那么你可能会考虑填充和使用一个持久化的TALL表，而不是像本例中那样使用子查询来动态生成它。本示例中的子查询生成一个从1到100的数字序列，有效地允许您在源表中将每行分割为100个定界值。如果您需要更多或更少，您可以轻松调整它。  
 
 
输出：
 
 | VALUE | 
 | ---------------- | 
 | somethingA | 
 | somethingB | 
 | somethingC | 
 | somethingElseA | 
 | somethingElseB | 
 
以下是  SQLFiddle  演示
 
 
 
 
 
 
查询可以用持久性计数表查看
  SELECT SUBSTRING_INDEX（SUBSTRING_INDEX（t.values，'，'，nn），'，'，-1）value 
 FROM table1 t CROSS JOIN tally n 
 WHERE nn <= 1 +（LENGTH（t.values） -  LENGTH（REPLACE（t.values，'，'，''）））
 ORDER BY值
  
以下是  SQLFiddle  演示
 
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)? 

(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)
解决方案
You can do it with pure SQL like this
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.

Output:
|          VALUE |
|----------------|
|     somethingA |
|     somethingB |
|     somethingC |
| somethingElseA |
| somethingElseB |
Here is SQLFiddle demo



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

                        
这篇关于SQL拆分逗号分隔的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！