请问这个SQL如何写,谢谢

[TGZ]

表A

F1 F2
============
001 40
001 10
001 20
002 5
003 7
003 8

要求结果:

F1 F2 No.
==============
001 40 1
001 10 2
001 20 3
002 5 1
003 7 1
003 8 2

用一个SQL写,如何写呢,谢谢

[tiger_hs]

select f1,f2,rownum No
from (
select f1,f2
from table_x
where ....
order by ... )

[hyrongg]

SQL> insert into tbl select '001',40 from dual
2 union all select '001',10 from dual
3 union all select '001',20 from dual
4 union all select '002',5 from dual
5 union all select '003',7 from dual
6 union all select '003',8 from dual;

6 rows inserted

SQL> select * from tbl;

F1 F2
---------- ------
001 40
001 10
001 20
002 5
003 7
003 8

6 rows selected
SQL> select f1,f2,row_number()over(partition by f1 order by f1) from tbl;

F1 F2 ROW_NUMBER()OVER(PARTITIONBYF1
---------- ------ ------------------------------
001 40 1
001 10 2
001 20 3
002 5 1
003 7 1
003 8 2

6 rows selected