SQL 在 Postgres 中将列拆分为多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29419993/
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
Split column into multiple rows in Postgres
提问by mgoldwasser
Suppose I have a table like this:
假设我有一张这样的表：
subject | flag
----------------+------
this is a test | 2
subjectis of type text, and flagis of type int. I would like to transform this table to something like this in Postgres:
subject是类型text，并且flag是类型int。我想在 Postgres 中将此表转换为类似的内容：
token | flag
----------------+------
this | 2
is | 2
a | 2
test | 2
Is there an easy way to do this?
是否有捷径可寻？
回答by Erwin Brandstetter
In Postgres 9.3+ use a LATERALjoin:
在 Postgres 9.3+ 中使用LATERAL连接：
SELECT s.token, flag
FROM tbl t, unnest(string_to_array(t.subject, ' ')) s(token)
WHERE flag = 2;
Note that the shorthand form of a LATERALjoin only returns rows, if unnest()actually returns row(s).
请注意LATERAL，如果unnest()实际返回行，联接的简写形式仅返回行。
You could also use regexp_split_to_table(), but that's typically slower because regular expression matching costs a bit more.
Related:
您也可以使用regexp_split_to_table()，但这通常会更慢，因为正则表达式匹配的成本更高。
有关的：
回答by Matt
I think it's not necessary to use a join, just the unnest()function in conjunction with string_to_array()should do it:
我认为没有必要使用连接，只需将unnest()函数与 with 结合即可string_to_array()：
SELECT unnest(string_to_array(subject, ' ')) as "token", flag FROM test;
unnest | token
-------+-------
this | 2
is | 2
a | 2
test | 2
