postgresql 如何在postgres数据库中将一对多关系聚合成新列

avwztpqn 于 2023-01-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(206)

我有这个疑问

select cre.id ,string_agg(ae.name, ', ') as agents
from call_report_entity cre 
join report_agents_entity rae on cre.id = rae."reportId" 
join agent_entity ae on rae."agentId" = ae.id 
group by cre .id
order by cre.id desc

结果：

但我需要把结果汇总为：

我怎样才能达到这个结果？

postgresql

来源：https://stackoverflow.com/questions/74981292/how-to-aggregate-one-to-many-relation-into-new-columns-in-postgres-database

1条答案

按热度按时间

llmtgqce1#

对于每个ID的固定最大代理数，您可以使用row_number和条件聚合进行透视：

select id,
    max(name) filter(where rn = 1) agent_1,
    max(name) filter(where rn = 2) agent_2,
    max(name) filter(where rn = 3) agent_3
from (
    select cre.id, ae.name,
        row_number() over(partition by cre.id order by ae.name) rn
    from call_report_entity cre 
    join report_agents_entity rae on cre.id = rae."reportId" 
    join agent_entity ae on rae."agentId" = ae.id
) t
group by id
order by id desc

赞(0）回复(0）举报 2023-01-05

我来回答

postgresql 如何在postgres数据库中将一对多关系聚合成新列

1条答案

相关问题

热门标签

最新问答