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

问题描述

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

某事A、某事B、某事C别的东西A，别的东西B

并且我希望结果采用每个值，并创建一行:

somethingA某事B某事C别的东西A别的东西B

如何在 SQL (MySQL) 中执行此操作?

(我试过谷歌搜索内爆"和横向视图"，但这些似乎没有出现相关问题.所有相关的 SO 问题都试图做更复杂的事情)

解决方案

你可以像这样用纯 SQL 做到这一点

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) 值FROM table1 t CROSS JOIN(选择 a.N + b.N * 10 + 1 n从(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),(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按 n 排序) nWHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))按值排序

注意:诀窍是利用tally(numbers)表和一个非常方便的MySQL函数SUBSTRING_INDEX().如果您进行了大量此类查询(拆分)，那么您可能会考虑填充和使用持久化的计数表，而不是像本例中那样使用子查询即时生成它.此示例中的子查询生成从 1 到 100 的数字序列，有效地允许您在源表中的每行拆分多达 100 个分隔值.如果您需要更多或更少，您可以轻松调整它.

输出:

<前>|价值 ||----------------||某事A ||某事B ||东西C ||别的东西A ||别的东西B |

这是SQLFiddle演示

<小时>

这是带有持久化计数表的查询的外观

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) 值FROM table1 t CROSS JOIN Tally nWHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))按值排序

这是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屋！