Bagaimana untuk Menghapuskan Nilai Dipisahkan Koma dalam PostgreSQL dan Mengekalkan Susunan Elemen?

Peluasan dengan nombor elemen dalam PostgreSQL

Soalan:

Menggunakan fungsi unnest() untuk mengembangkan lajur yang mengandungi nilai dipisahkan koma hanya akan mengembalikan elemen itu sendiri, bukan kedudukan asalnya dalam rentetan.

Sasaran:

Dapatkan elemen dan nombor jujukannya dalam rentetan sumber.

Penyelesaian:

PostgreSQL 14 dan ke atas

Gunakan fungsi string_to_table() dan klausa 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 dan lebih tinggi

Tambah klausa WITH ORDINALITY pada fungsi 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

Tinggalkan klausa row_number() dalam fungsi 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

Buat fungsi tersuai f_unnest_ord() untuk mengekstrak elemen dan nombor sirinya:

<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>

Kemudian gunakan ini untuk mengembangkan nilai dengan nombor ordinal:

<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>

Atas ialah kandungan terperinci Bagaimana untuk Menghapuskan Nilai Dipisahkan Koma dalam PostgreSQL dan Mengekalkan Susunan Elemen?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!