PostgreSQL中带元素编号的展开
问题:
使用unnest()
函数展开包含逗号分隔值的列,只会返回元素本身,而不会返回它们在字符串中的原始位置。
目标:
获取元素及其在源字符串中的序号。
解决方案:
使用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>
将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>
在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>
创建一个自定义函数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中文网其他相关文章!