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;
array_to_string(array_agg(distinct g.c_name), ’ , '):将数组转换为字符串,用“,”分隔。(有点类似于Mysql的group_concat()函数)。
array_agg(distinct 想要合并的数据):将想要的数据变成数组。
string_to_array(c.c_groups, ‘,’):将字符串按照“,”分隔成数组。
any (String(varchar)::int[]):将字符串转换为整形。
id = any(List):id的值存在于List中,注意List要和id为同种类型。
本人粗浅理解,要是有出错的地方希望各位大佬可以指正!!!