抛砖引玉:
select a.*,(select sum(2) from table1 where px=a.px and bm<=a.bm) as xh from table1 a order by px,xh;
select a.*,(select sum(2) from table1 where px=a.px and bm<=a.bm) as xh from table1 a order by px,xh;
BM PX XH
1 11 1
2 12 1
3 12 2
4 13 1
5 13 2
6 13 3
7 14 1
8 15 1
9 15 2
结果显示:BM PX XH
1 11 001
2 12 001
3 12 002
4 13 001
5 13 002
6 13 003
7 14 001
8 15 001
9 15 002
2 (
3 BM VARCHAR2(10),
4 PX VARCHAR2(10),
5 XH VARCHAR2(10)
6 )
7 ;
SQL> insert into aaa values('1','11','');1 row insertedSQL> insert into aaa values('2','11','');1 row insertedSQL> insert into aaa values('3','12','');1 row insertedSQL> insert into aaa values('4','13','');1 row insertedSQL> insert into aaa values('5','13','');1 row insertedSQL> insert into aaa values('6','14','');1 row insertedSQL> insert into aaa values('7','15','');1 row insertedSQL> insert into aaa values('8','16','');1 row insertedSQL> insert into aaa values('9','15','');1 row insertedSQL> insert into aaa values('10','15','');1 row inserted
SQL> select T.*,
2 to_char((select sum(1) from aaa where px=T.px and bm<=T.bm),'000') as XH from aaa T
3 ;BM PX XH XH
---------- ---------- ---------- ----
1 11 001
2 11 002
3 12 001
4 13 001
5 13 002
6 14 001
7 15 002
8 16 001
9 15 003
10 15 00110 rows selected