update (select type,rownum as num from tab group by id where rownum<4) set type=num; update (select type from tab group by id where rownum>=4) set type=3;
SQL> select * from aa; ID NO TYPE ---------- ---------- ---------- 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
SQL> select * from aa; ID NO TYPE ---------- ---------- ---------- 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.
SQL> select id,no,decode(serial_no,1,1,2,2,3) type from 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.
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.