假设我有一张这样的表:
Suppose I have a table like this:
subject | flag ----------------+------ this is a test | 2subject 是 text 类型,flag 是 int 类型.我想在 Postgres 中将此表转换为类似的内容:
subject is of type text, and flag is of type int. I would like to transform this table to something like this in Postgres:
token | flag ----------------+------ this | 2 is | 2 a | 2 test | 2有没有简单的方法可以做到这一点?
Is there an easy way to do this?
推荐答案在 Postgres 9.3+ 中使用 LATERAL join:
In Postgres 9.3+ use a LATERAL join:
SELECT s.token, flag FROM tbl t, unnest(string_to_array(t.subject, ' ')) s(token) WHERE flag = 2;这是一个隐式的 LATERAL 连接.如果 unnest() 不返回任何行(空或 NULL subject),结果将根本没有行.使用 LEFT JOIN unnest(...) i ON true 总是从 tbl 返回行.见:
It's an implicit LATERAL join. If unnest() does not return any rows (empty or NULL subject), the result will be no row at all. Use LEFT JOIN unnest(...) i ON true to always return rows from tbl. See:
- 什么是LATERAL JOIN 和 PostgreSQL 子查询的区别?
您也可以使用 regexp_split_to_table(),但这通常会更慢,因为正则表达式匹配的成本更高.相关:
You could also use regexp_split_to_table(), but that's typically slower because regular expression matching costs a bit more. Related:
- SQL 选择文本字段中包含子字符串的行
- 带有元素编号的 PostgreSQL unnest()
更多推荐
在 Postgres 中将列拆分为多行
发布评论