PostgreSQL 字符串拆分与合并案例

我就废话不多说了，大家还是直接看代码吧~

with person_name as (
select c.id,
array_to_string(array_agg(distinct p.c_name), ‘ , ‘) as p_name
from biz_notification_config c
join biz_notification_person p
on p.id =
any (string_to_array(c.persons, ‘,’)::int[])
group by c.id
),
group_name as (
select c.id,
array_to_string(array_agg(distinct g.c_name), ‘ , ‘) as g_name
from biz_notification_config c
join biz_notification_group g
on g.id = any (string_to_array(c.c_groups, ‘,’)::int[])
group by c.id
)
select config.*, person_name.p_name, group_name.g_name
from biz_notification_config config
left join person_name
on config.id = person_name.id
left join group_name
on config.id = group_name.id;