如何在 PostgreSQL 中取消嵌套逗號分隔值並保留元素順序？

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中文網其他相關文章！