在 Postgresql 中拆分逗号分隔的字段并对所有结果表执行 UNION ALL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9232572/
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
Splitting a comma-separated field in Postgresql and doing a UNION ALL on all the resulting tables
提问by John Horton
I have a table that contains a field of comma separated strings:
我有一个包含逗号分隔字符串字段的表：
ID | fruits
-----------
1 | cherry,apple,grape
2 | apple,orange,peach
I want to create a normalized version of the table, like this:
我想创建表格的规范化版本，如下所示：
ID | fruits
-----------
1 | cherry
1 | apple
1 | grape
2 | apple
2 | orange
2 | peach
The postgresql 8.4 documentation describes a regexp_split_to_table function that can turn a single table:
postgresql 8.4 文档描述了一个可以转单表的 regexp_split_to_table 函数：
SELECT foo
FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog',E'\s+') AS
foo;
which gives you this:
这给了你这个：
foo
--------
the
quick
brown
fox
jumped
over
the
lazy
dog
(9 rows)
But that is just for a single field. What I want to do is some kind UNION applied to all the tables generated by splitting each field. Thank you.
但这只是针对单个领域。我想要做的是某种 UNION 应用于通过拆分每个字段生成的所有表。谢谢你。
回答by Michael Fredrickson
This should give you the output you're looking for:
这应该给你你正在寻找的输出：
SELECT
yourTable.ID,
regexp_split_to_table(yourTable.fruits, E',') AS split_fruits
FROM yourTable
EDIT: Fixed the regex.
编辑：修复了正则表达式。
