带有$ delimiter的postgres联接列表

Question

从这些表格中：

select group, ids 
from some.groups_and_ids; 

结果：

group   | group_ids
     ---+----  
winners | 1$4
losers  | 4  
others  | 2$3$4

和：

select id,name from some.ids_and_names; 

id | name  
---+----  
1  | bob  
2  | robert
3  | dingus
4  | norbert

你将如何返回类似如下的内容：

winners | bob, norbert
losers  | norbert 
others  | robert, dingus, norbert

Answer 1

with normalized  (group_name, id) as (
  select group_name, unnest(string_to_array(group_ids,'$')::int[])
  from groups_and_ids
)
select n.group_name, string_agg(p.name,',' order by p.name)
from normalized n
  join ids_and_names p on p.id = n.id
group by n.group_name;

第一部分( common table expression)通过在groups_and_ids表上创建一个适当的视图来规范化您的破碎的表设计。然后，实际的查询将ids_and_names表连接到您的组的规范化版本，并再次聚合名称。

注我将group重命名为group_name，因为group是一个保留关键字。

SQLFiddle：http://sqlfiddle.com/#!15/2205b/2

Answer 2

有可能重新设计你的数据库吗？将所有group_ids放在一列中会使情况变得很困难。如果您的表是例如

group | group_id

winners | 1
winners | 4
losers | 4

等等，这会非常简单。实际上，下面的查询可以做到这一点，尽管我不太愿意发布它，因为它鼓励糟糕的数据库设计(IMHO)！

附注：我冒失地重命名了一些列，因为它们是保留字。你可以逃避它们，但为什么要让自己的生活变得困难呢？

select group_name,array_to_string(array_agg(username),', ') -- array aggregation and make it into a string
from

(
select group_name,theids,username
from ids_and_names
inner join

(select group_name,unnest(string_to_array(group_ids,'$')) as theids -- unnest a string_to_array to get rows

from groups_and_ids) i
on i.theids = cast(id as text)) a
group by group_name