請幫助此要求是加入小計的總和。
我有一個這樣的結果表。 圖片表
CID | STORECODE | STORENAME | CONTRACTNO | SUBTOTAL
---------------------------------------------------------
222 | SCI-SCG5 | S2 | 111 | 657,534.20
221 | SCI-SCG5 | S2 | 110 | 700,000
如何得到這樣的結果：
CID | STORECODE | STORENAME | CONTRACTNO | SUBTOTAL
----------------------------------------------------------
222,221 | SCI-SCG5 | S2 | 111,110 | 1.357.534,20
或者
STORECODE | STORENAME | SUBTOTAL
--------------------------------
SCI-SCG5 | S2 | 1.357.534,20
uj5u.com熱心網友回復：
您可以使用listagg在 oracle 中的組內聚合字串：
select listagg(CID,', ') within group( order by CID desc) as CID,
storecode,
storename,
listagg(contractno,', ') within group( order by contractno desc) as CID,
sum(subtotal) as subtotal
from yourtable
group by storecode, storename;
小提琴
uj5u.com熱心網友回復：
不同型別的聚合：
SQL> with test (cid, storecode, storename, contractno, subtotal) as
2 (select 222, 'SCI-SCG5', 'S2', 111, 657534.20 from dual union all
3 select 221, 'SCI-SCG5', 'S2', 110, 700000 from dual
4 )
5 select
6 listagg(cid, ',') within group (order by cid) cid,
7 storecode,
8 storename,
9 listagg(contractno, ',') within group (order by contractno) contractno,
10 sum(subtotal) subtotal
11 from test
12 group by storecode, storename;
CID STORECOD ST CONTRACTNO SUBTOTAL
--------------- -------- -- -------------------- ----------
221,222 SCI-SCG5 S2 110,111 1357534,2
SQL>
SQL> with test (cid, storecode, storename, contractno, subtotal) as
2 (select 222, 'SCI-SCG5', 'S2', 111, 657534.20 from dual union all
3 select 221, 'SCI-SCG5', 'S2', 110, 700000 from dual
4 )
5 select storecode, storename, sum(subtotal) subtotal
6 from test
7 group by storecode, storename;
STORECOD ST SUBTOTAL
-------- -- ----------
SCI-SCG5 S2 1357534,2
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/391751.html
下一篇：sqlgroup按年齡范圍更新列