如何将整型数组映射到单个concat字符串?

Question

我有这些表:

names
id | name
7  | 'a'
8  | 'b'
9  | 'c'
group_names
id | group_of_names
1  | '7'
2  | '9,8'
3  | '7,8,9'

如何构建一个select，返回它们的名称，以分号分隔，保留原始顺序，如:

id | single_text
1  | 'a'
2  | 'c;b'
3  | 'a;b;c'

I manage to do

select g.id, string_to_array(g.group_of_names,',')::int[] from group_names g;
id | string_to_array
1  | {7}
2  | {9,8}
3  | {7,8,9}

但是我不知道如何，返回几个数组，对于每个数组，根据它们的id连接文本

Answer 1

如果结果字符串的顺序无关:

select g.id, string_agg(n.name, ';')
from group_names g
join names n
on n.id = any(string_to_array(g.group_of_names, ',')::int[])
group by g.id

否则:

select g.id, string_agg(n.name, ';' order by ord)
from names n
join (
select id, elem, ord
from group_names
cross join regexp_split_to_table(group_of_names, ',') 
with ordinality as arr(elem, ord)
) g
on n.id = g.elem::int
group by g.id

在db<>fiddle中测试。

在Postgres 14+中，你可以使用string_to_table()代替regexp_split_to_table()。

Answer 2

您可以这样做:使用ANY运算符检查n.id值是否在group_of_names的数组中

SELECT gn.id, string_agg(n.name, ';') AS single_text
FROM names n
INNER JOIN group_names gn ON n.id::text = ANY(string_to_array(gn.group_of_names, ','))
GROUP BY gn.id
ORDER BY gn.id;

或者这样:使用查询和unnest()函数将数组扩展为一组行。

SELECT gn.id, string_agg(n.name, ';')
FROM names n
INNER JOIN (SELECT g.id, unnest(string_to_array(g.group_of_names, ',')::int[]) AS name_id
FROM group_names g) AS gn ON n.id = gn.name_id
GROUP BY gn.id
ORDER BY gn.id;     

Answer 3

SELECT g.id, string_agg(n.name,';') AS single_text
FROM group_names AS g
CROSS JOIN LATERAL regexp_split_to_table (g.group_of_names, ',') AS gn(element)
INNER JOIN names AS n
ON n.id :: text = gn.element
GROUP BY g.id