你要这样的结果?二大队 2005B00156 1 100 0 二大队 2005B00156 1 300 0 二大队 2005B00156 1 200 0 二大队 2005B00156 1 1000 1600 一大队 2005B00313 1 100 100 没测试过,你试下吧:select dd,bh,bz,decode(max(zh) over(partition by dd,bh,bz),zh,zh,0) zh from (select dd,bh,bz,sum(je) over(partition by dd,bh,bz order by je) zh from tablename t)
select dd,bh,bz,je,zh=case when (select count(1) from ( select *,te=(select min(je) from t a where a.bh=b.bh) from t b) a where a.dd=b.dd)>=2 and je=te then 0 else te end from ( select *,te=(select min(je) from t a where a.bh=b.bh) from t b) b
select a.dd, a.bh, a.bz, b.je from table a, (select sum(nvl(a.je,0)) as je,max(rowid) as rowid from table b group by dd,bh,bz) b where a.rowid(+) = b.rowid order by dd,bh,bz
还有 其他字段不行呀 duanzilin(寻)
二大队 2005B00156 1 1000 1100
一大队 2005B00313 1 100 100
这是结果
select dd,bh,bz,sum(je) over(partition by dd,bh,bz order by je) zh from tablename t
二大队 2005B00156 1 100 100
二大队 2005B00156 1 1000 1100
一大队 2005B00313 1 100 100
想要的
二大队 2005B00156 1 100 0
二大队 2005B00156 1 1000 1100
一大队 2005B00313 1 100 100
二大队 2005B00156 1 1000 1100
一大队 2005B00313 1 100 100 --这一条怎么不是0
二大队 2005B00156 1 300 0
二大队 2005B00156 1 200 0
二大队 2005B00156 1 1000 1600
一大队 2005B00313 1 100 100
没测试过,你试下吧:select dd,bh,bz,decode(max(zh) over(partition by dd,bh,bz),zh,zh,0) zh from
(select dd,bh,bz,sum(je) over(partition by dd,bh,bz order by je) zh from tablename t)
(
select *,te=(select min(je) from t a where a.bh=b.bh)
from t b) a where a.dd=b.dd)>=2 and je=te then 0
else te end
from
(
select *,te=(select min(je) from t a where a.bh=b.bh)
from t b) b
from table a,
(select sum(nvl(a.je,0)) as je,max(rowid) as rowid from table b group by dd,bh,bz) b
where a.rowid(+) = b.rowid
order by dd,bh,bz