select id,no,row_number() over(partition by id order by id) as "type" from tb
解决方案 »
- VMware7虚拟机怎么连接服务器上的数据库?
- oracle drop table 中间被终止会删除表中的数据吗...
- oracle 驱动寻找
- Oracle触发器帮忙看一下!!!
- 如何在ORACLE的游标中动态地构造SQL语句啊?
- 各位老大,能否提供一个关于触发器和存储过程的基本原理和操作手册之类参考的东西啊
- 新手求oracle8i的中文帮助一份。
- 怎样提高oracle的开库速度?
- 我的oracle在sqlplus下打的开,但用database administration下的DBA studio却打不开此数据库。急!
- 高分求救:oracle 不能随系统启动而自动启动???
- 求一个SQL语句
- tnsnames中的数据库连接串经常会莫名其妙的丢失,请问会是什么原因?
update (select type from tab group by id where rownum>=4) set type=3;
---------- ---------- ----------
1 10
1 20
1 50
2 30
2 15SQL> select id,no,
2 row_number()
3 over (partition by id order by no) serial_no
4 from aa
5 / ID NO SERIAL_NO
---------- ---------- ----------
1 10 1
1 20 2
1 50 3
2 15 1
2 30 2SQL>
select id,no,row_number() over(partition by id order by no) as "type" from tb
---------- ---------- ----------
1 80
1 20
1 50
2 30
2 15
1 1006 rows selected.SQL> select id,no,decode(serial_no,1,1,2,2,3) from (
2 select id,no,
3 row_number()
4 over (partition by id order by no) serial_no
5 from aa
6 )
7 / ID NO DECODE(SERIAL_NO,1,1,2,2,3)
---------- ---------- ---------------------------
1 20 1
1 50 2
1 80 3
1 100 3
2 15 1
2 30 26 rows selected.
2 (select id,no,row_number() over (partition by id order by no) serial_no from aa)
3 / ID NO TYPE
---------- ---------- ----------
1 20 1
1 50 2
1 80 3
1 100 3
2 15 1
2 30 26 rows selected.
SQL> select * from aa; ID NO TYPE
---------- ---------- ----------
1 80
1 20
1 50
2 30
2 15
1 1006 rows selected.SQL> update aa a set a.type=(
2 select b.type from
3 (select myid,decode(serial_no,1,1,2,2,3) type from
4 (select rowid myid,row_number() over (partition by id order by no) serial_no from aa)) b
5 where a.rowid=b.myid)
6 /6 rows updated.SQL> select * from aa; ID NO TYPE
---------- ---------- ----------
1 80 3
1 20 1
1 50 2
2 30 2
2 15 1
1 100 36 rows selected.