17,377
社区成员
发帖
与我相关
我的任务
分享
with t as
(select 1 seq, '001' cno, 1 amt from dual
union all
select 2 seq, '002' cno, 4 amt
from dual
union all
select 3 seq, '002' cno, 6 amt
from dual
union all
select 4 seq, '001' cno, 2 amt
from dual
union all
select 5 seq, '003' cno, 3 amt from dual)
select seq, cno, sum(amt) from t group by grouping sets((seq, cno), cno);
with t1 as
(
select 1 c1,'001' c2,1 c3 from dual union all
select 2 c1,'002' c2,4 c3 from dual union all
select 3 c1,'001' c2,6 c3 from dual union all
select 4 c1,'002' c2,2 c3 from dual
)
select c1,
case when c1 is null and c2 is not null then c2||'合计'
when c1 is null and c2 is null then '总计'
else c2 end c2,
sum(c3) c3
from t1
group by rollup(c2,c1)
order by c2,c1
c1 c2 c3
----------------------------------
1 1 001 1
2 3 001 6
3 001合计 7
4 2 002 4
5 4 002 2
6 002合计 6
7 总计 13
---rollup函数
SELECT ID,CASE WHEN ID IS NULL THEN '合计' ELSE CARD_ID END ,MONEY FROM
(SELECT T.CARD_ID,T.ID, SUM(T.MONEY) MONEY
FROM TEST1 T
GROUP BY ROLLUP(T.CARD_ID,T.ID) ) d
WHERE CARD_ID IS NOT NULL