字段 fa fb fc
数据
a1 b2 -1
a1 b2 -2
a1 b2 -3 a1 b2 1
a1 b2 2
a1 b2 3 a1 b1 -1
a1 b1 -2
a1 b1 -3 a1 b1 1
a1 b1 2
a1 b1 3
预想结果
a1 b1 -6
a1 b1 6
a1 b2 -6
a1 b2 6
如何根据fa,fb进行统计(并且也要根据fc的正负)
数据
a1 b2 -1
a1 b2 -2
a1 b2 -3 a1 b2 1
a1 b2 2
a1 b2 3 a1 b1 -1
a1 b1 -2
a1 b1 -3 a1 b1 1
a1 b1 2
a1 b1 3
预想结果
a1 b1 -6
a1 b1 6
a1 b2 -6
a1 b2 6
如何根据fa,fb进行统计(并且也要根据fc的正负)
union all
select fa,fb,sum(fc) fc from table where fc>0 group by fa,fb
select 'a1' fa,'b2' fb,-1 fc from dual
union all
select 'a1' fa,'b2' fb,-2 fc from dual
union all
select 'a1' fa,'b2' fb,-3 fc from dual
union all
select 'a1' fa,'b2' fb,1 fc from dual
union all
select 'a1' fa,'b2' fb,2 fc from dual
union all
select 'a1' fa,'b2' fb,3 fc from dual
union all
select 'a1' fa,'b1' fb,-1 fc from dual
union all
select 'a1' fa,'b1' fb,-2 fc from dual
union all
select 'a1' fa,'b1' fb,-3 fc from dual
union all
select 'a1' fa,'b1' fb,1 fc from dual
union all
select 'a1' fa,'b1' fb,2 fc from dual
union all
select 'a1' fa,'b1' fb,3 fc from dual
)select fa,fb,sum(fc) from temp where fc < 0 group by fa,fb
union all
select fa,fb,sum(fc) from temp where fc > 0 group by fa,fb
SQL> with tab as (
2 select 'a1' fa, 'b2' fb, -1 fc from dual union all
3 select 'a1' fa, 'b2' fb, -2 fc from dual union all
4 select 'a1' fa, 'b2' fb, -3 fc from dual union all
5 select 'a1' fa, 'b2' fb, 1 fc from dual union all
6 select 'a1' fa, 'b2' fb, 2 fc from dual union all
7 select 'a1' fa, 'b2' fb, 3 fc from dual union all
8 select 'a1' fa, 'b1' fb, -1 fc from dual union all
9 select 'a1' fa, 'b1' fb, -2 fc from dual union all
10 select 'a1' fa, 'b1' fb, -3 fc from dual union all
11 select 'a1' fa, 'b1' fb, 1 fc from dual union all
12 select 'a1' fa, 'b1' fb, 2 fc from dual union all
13 select 'a1' fa, 'b1' fb, 3 fc from dual
14 )
15 select fa, fb, sum(fc) from tab group by fa, fb, sign(fc)
16 ;FA FB SUM(FC)
-- -- ----------
a1 b2 -6
a1 b2 6
a1 b1 6
a1 b1 -6SQL>
--或者
with tb as(
select 'a1' fa,'b1' fb,-1 fc from dual union all
select 'a1','b1',-2 from dual union all
select 'a1','b2',1 from dual union all
select 'a1','b2',2 from dual)
select fa,fb,sum(fc)
from tb
group by fa,fb,(sign(fc))