sql - 在 oracle 中对列进行分组

Question

我的表中有三列： Amount, OrderNumber,Customerid

对于每个customerid将有ordernumber和amount。现在我需要显示customerid，Ordernumber和Amount（每个客户 ID 的总金额）。

custid  srcnumber   amount
112     4344        20
112     7678        10
112     8766        30
34      6577        15
34      4566        5

预期的：

custid  srcnumber   amount
112     4344        60
112     7678        60
112     8766        60
34      6577        20
34      4566        20

标签： sqloraclegroup-byaggregationanalytic-functions

Answer 1

使用sum() over (partition by ..)分析函数总结每行的金额：

 select Customerid as custid,
        OrderNumber as srcnumber,
        sum(amount) over ( partition by Customerid ) as amount
   from tab
  order by custid desc

Demo