首页 >数据库 >mysql教程 >如何在 PostgreSQL 中取消嵌套逗号分隔值并保留元素顺序?

如何在 PostgreSQL 中取消嵌套逗号分隔值并保留元素顺序?

Patricia Arquette
Patricia Arquette原创
2025-01-23 04:22:11770浏览

How to Unnest Comma-Separated Values in PostgreSQL and Preserve Element Order?

PostgreSQL中带元素编号的展开

问题:

使用unnest()函数展开包含逗号分隔值的列,只会返回元素本身,而不会返回它们在字符串中的原始位置。

目标:

获取元素及其在源字符串中的序号。

解决方案:

PostgreSQL 14及更高版本

使用string_to_table()函数和WITH ORDINALITY子句:

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM tbl t
LEFT JOIN LATERAL string_to_table(t.elements, ',') WITH ORDINALITY AS a(elem, nr) ON true;</code>

PostgreSQL 9.4及更高版本

WITH ORDINALITY子句添加到unnest()函数:

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM tbl AS t
LEFT JOIN LATERAL unnest(string_to_array(t.elements, ',')) WITH ORDINALITY AS a(elem, nr) ON true;</code>

PostgreSQL 8.4 - 9.3

row_number()函数中省略ORDER BY子句:

<code class="language-sql">SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t;</code>

PostgreSQL 8.1 - 8.4

创建一个自定义函数f_unnest_ord()来提取元素及其序号:

<code class="language-sql">CREATE FUNCTION f_unnest_ord(anyarray, OUT val anyelement, OUT ordinality integer)
RETURNS SETOF record
LANGUAGE sql IMMUTABLE AS
'SELECT [i], i - array_lower(,1) + 1
FROM generate_series(array_lower(,1), array_upper(,1)) i';</code>

然后使用它来展开带有序号的值:

<code class="language-sql">SELECT id, arr, (rec).*
FROM (SELECT *, f_unnest_ord(arr) AS rec FROM (VALUES (1, '{a,b,c}'::text[])...) t) sub;</code>

以上是如何在 PostgreSQL 中取消嵌套逗号分隔值并保留元素顺序?的详细内容。更多信息请关注PHP中文网其他相关文章!

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn