sql求救

[zhaozheng821018]

原始表（字段都是数据类型的）
A B c
2012 1 20
2012 2 0
2012 3 50
2012 4 0
2012 5 0
2012 6 0
2012 7 0
2012 8 80
2012 9 0
2012 10 0
2012 11 90
2012 12 0

有没有可能使用sql语句是变成这样的结果（最好以试图的形式出现）
A B c
2012 1 20
2012 2 20
2012 3 50
2012 4 50
2012 5 50
2012 6 50
2012 7 50
2012 8 80
2012 9 80
2012 10 80
2012 11 90
2012 12 90

不能用存储过程

[zhaozheng821018]

额，自己搞定

select b.a,b.b,case when c<>0 then c else (select sum(c) from view_tst_aaa a where  a.b<=b.b and a.b>=(select max(c.b) from view_tst_aaa c where c.c>0 and c.b<=b.b)) end 
from view_tst_aaa b

[大明湖畔_帅锅]

可以了？

[大明湖畔_帅锅]

这个c都是逐个变大的吧？ 除0外 下面的值都是大于上面的值？

[ssqtjffcu]

create table T
(
  A VARCHAR2(4),
  B INTEGER,
  C INTEGER
)
;
insert into T (A, B, C)
values ('2012', 1, 20);
insert into T (A, B, C)
values ('2012', 2, 0);
insert into T (A, B, C)
values ('2012', 3, 50);
insert into T (A, B, C)
values ('2012', 4, 0);
insert into T (A, B, C)
values ('2012', 5, 0);
insert into T (A, B, C)
values ('2012', 6, 0);
insert into T (A, B, C)
values ('2012', 7, 0);
insert into T (A, B, C)
values ('2012', 8, 80);
insert into T (A, B, C)
values ('2012', 9, 0);
insert into T (A, B, C)
values ('2012', 10, 0);
insert into T (A, B, C)
values ('2012', 11, 90);
insert into T (A, B, C)
values ('2012', 12, 0);
commit;

select t.a,t.b,t1.c
  from t,
       (select a, b, lead(b, 1) over(order by a, b) r, c from t where c <> 0) t1
 where t.b >= t1.b
   and (t.b < t1.r or t1.r is null)

A                                          B                                       C
---- --------------------------------------- ---------------------------------------
2012                                       1                                      20
2012                                       2                                      20
2012                                       3                                      50
2012                                       4                                      50
2012                                       5                                      50
2012                                       6                                      50
2012                                       7                                      50
2012                                       8                                      80
2012                                       9                                      80
2012                                      10                                      80
2012                                      11                                      90
2012                                      12                                      90
 
12 rows selected

[vingo2000]

select a,b,(select max(c) from tab t1 where t1.a = t.a and to_number(t1.b) >=to_number(t.b))
from tab t

[ssqtjffcu]

SELECT t.*,
       nvl(last_value(nullif(c, 0) ignore NULLS) over(ORDER BY b), 0) a1
  FROM t;
A                                          B                                       C         A1
---- --------------------------------------- --------------------------------------- ----------
2012                                       1                                      20         20
2012                                       2                                       0         20
2012                                       3                                      50         50
2012                                       4                                       0         50
2012                                       5                                       0         50
2012                                       6                                       0         50
2012                                       7                                       0         50
2012                                       8                                      80         80
2012                                       9                                       0         80
2012                                      10                                       0         80
2012                                      11                                      90         90
2012                                      12                                       0         90
 
12 rows selected
 

今天看到一个更简单的方法