关于汇总的语句如何写啊接触Oracle不太长,就需要开发一个项目
关于汇总的语句怎么写啊?接触Oracle不太长,就需要开发一个项目。
现在涉及到报表开发了,有些功能还是不太熟悉。
以下是碰到的问题
汇总之前的数据:
-----------------------------------------
编码 数量
aaa 2
aaa 3
bbb 2
bbb 1
aaa 3
ccc 1
ccc 4
--------------------------------------
汇总之后的效果:
编码 数量
aaa 5
bbb 3
aaa 3
ccc 5
---------------------------------------------------------
也就是按照编码出现的先后顺序,仅汇总相邻的编码一样的数量。
------解决思路----------------------
------解决思路----------------------
顺序的逻辑遇到集的处理就会有点小挑战,呵~
现在涉及到报表开发了,有些功能还是不太熟悉。
以下是碰到的问题
汇总之前的数据:
-----------------------------------------
编码 数量
aaa 2
aaa 3
bbb 2
bbb 1
aaa 3
ccc 1
ccc 4
--------------------------------------
汇总之后的效果:
编码 数量
aaa 5
bbb 3
aaa 3
ccc 5
---------------------------------------------------------
也就是按照编码出现的先后顺序,仅汇总相邻的编码一样的数量。
------解决思路----------------------
with t as
(select 'a' code, 2 val
from dual
union all
select 'a' code, 3 val
from dual
union all
select 'b' code, 2 val
from dual
union all
select 'b' code, 1 val
from dual
union all
select 'a' code, 3 val
from dual
union all
select 'c' code, 1 val
from dual
union all
select 'c' code, 4 val
from dual)
select t3.code,
(select sum(val)
from (select t.*, rownum rn from t) t4
where t4.rn > t3.st_cd
and t4.rn <= t3.rn) total
from (select t2.*, nvl(lag(rn) over(order by rn), 0) st_cd
from (select t1.*, lead(code) over(order by rn) next_cd
from (select t.*, rownum rn from t) t1) t2
where code <> next_cd
or next_cd is null) t3
order by rn;
------解决思路----------------------
顺序的逻辑遇到集的处理就会有点小挑战,呵~
with t as
(select 'a' code, 2 val
from dual
union all
select 'a' code, 3 val
from dual
union all
select 'b' code, 2 val
from dual
union all
select 'b' code, 1 val
from dual
union all
select 'a' code, 3 val
from dual
union all
select 'c' code, 1 val
from dual
union all
select 'c' code, 4 val
from dual)
select code,sum(val)
from (select code,val,
nvl((select max(rn) from (select code,val,rownum rn from t) b where code!=a.code and rn<a.rn),0) next_code --此处才产生虚拟分组序号
from (select code,val,rownum rn from t) a)
group by code,next_code
order by next_code;