在Postgresql中按分组

debugcn 发表于 Dev

Shyam Solanki

我有下表。有金额和一个付费字段

partner               sale_order   Amount     Paid             
Administrator         s0002         100       done         
Administrator         s0007         100       
Administrator         s0004         100       done
Administrator         s0009         100
Administrator         s0003         100
Demo User             s0001         200       done
Demo User             s0005         200       done
Demo User             s0008         200

我的愿望输出将是

   partner          amount       paid_amount
Administrator        500           200
    s0002            100           100 
    s0007            100 
    s0004            100           100  
    s0009            100
    s0003            100
    s0006            100
Demo User            600           400  
    s0001            200           200 
    s0008            200           200  
    s0005            200

这可能吗？这是一个编辑。是否可以进行第二次编辑？

罗曼·佩卡（Roman Pekar）

with cte(partner, amount, sort, sort2) as (
    select
        partner, sum(amount), partner, 0
    from table1
    group by partner

    union all

    select
        sale_order, amount, partner, 1
    from table1
)
select data, amount
from cte
order by sort, sort2

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。