我的mytable1具有row_number整数和list_of_idsint[]列
具有ID整数和公司文本列的mytable2
mytable1的示例条目
1 | {633681,1278392,2320888,2200426}
2 | {2443842,2959599,3703823,3330376,915750,941736}
mytable2的示例条目
633681 | apple
1278392 | charmander
2320888 | apple
2200426 | null
2443842 | batman
我需要将值从mytable2反馈到mytable1中。这样，预期的输出将是
1 | {633681,1278392,2320888,2200426} | 2 apple, 1 charmander, 1 null
2 | {2443842,2959599,3703823,3330376,915750,941736} | 1 batman etc...
您需要取消嵌套的列表ids，mytable2使用未嵌套的列表进行合并ids，最后汇总回数据以获得的一行row_number。
select
row_number,
list_of_ids,
string_agg(format('%s %s', count, company), ', ' order by count desc, company)
from (
select
row_number,
list_of_ids,
coalesce(company, '<null>') as company,
count(*)
from (
select row_number, list_of_ids, unnest(list_of_ids) as id
from mytable1
) t1
join mytable2 t2 using(id)
group by 1, 2, 3
) s
group by 1, 2
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句