表A的数据如下
name seq parent
A 0 001
B 0 001
C 0 001
D 0 001
E 0 002
F 0 002
想把seq的字段更新一下,相同的parent记录seq按自然数排序
name seq parent
A 1 001
B 2 001
C 3 001
D 4 001
E 1 002
F 2 002
这样的语句改咋写啊?谢谢各位了
name seq parent
A 0 001
B 0 001
C 0 001
D 0 001
E 0 002
F 0 002
想把seq的字段更新一下,相同的parent记录seq按自然数排序
name seq parent
A 1 001
B 2 001
C 3 001
D 4 001
E 1 002
F 2 002
这样的语句改咋写啊?谢谢各位了
select a.*,row_number()over(partition by parent)rn from a)
where name=t.name)
- ---------- ---
A 0 001
B 0 001
C 0 001
D 0 001
E 0 002
F 0 0026 rows selected.SQL> update a t
2 set seq=(select count(*) from a where parent=t.parent and name<=t.name);6 rows updated.SQL> select * from a;N SEQ PAR
- ---------- ---
A 1 001
B 2 001
C 3 001
D 4 001
E 1 002
F 2 0026 rows selected.
---------- ---------- ----------
A 0 001
B 0 001
C 0 001
D 0 001
E 0 002
F 0 0026 rows selected.SQL> update test1
2 set seq=(
3 select rn from
4 (
5 select test1.*,row_number() over(partition by parent order by name) rn
6 from test1
7 ) x
8 where x.name=test1.name);6 rows updated.SQL> select * from test1;NAME SEQ PARENT
---------- ---------- ----------
A 1 001
B 2 001
C 3 001
D 4 001
E 1 002
F 2 0026 rows selected.
set seq=(
select rn from
(select tb.*,row_number() over(partition by parent order by name) rn
from tb
) t
where t.name=tb.name);
刚才我在试的过程中over()里面不加order by只用partition by的话会报错
SQL> select test1.*,row_number() over(partition by parent) rn
2 from test1;
select test1.*,row_number() over(partition by parent) rn
*
ERROR at line 1:
ORA-30485: missing ORDER BY expression in the window specification
加一个条件x.parent=test1.parent
SQL> select * from test1;NAME SEQ PARENT
---------- ---------- ----------
A 0 001
B 0 001
C 0 001
D 0 001
E 0 002
F 0 002
A 0 0027 rows selected.SQL> update test1
2 set seq=(
3 select rn from
4 (
5 select test1.*,row_number() over(partition by parent order by parent) rn
6 from test1
7 ) x
8 where x.name=test1.name and x.parent=test1.parent);7 rows updated.SQL> select * from test1;NAME SEQ PARENT
---------- ---------- ----------
A 1 001
B 4 001
C 3 001
D 2 001
E 1 002
F 3 002
A 2 0027 rows selected.
没测试过, 你可以调试一下。最好把创建脚本和插入数据的脚本发上来,这样才容易帮你测试。
^_^
set seq=(
select rn from
(select tb.*,row_number() over(partition by parent order by name) rn
from tb
) t
where t.name=tb.name);
因为partition by是分组,相当于group by,所以如果over前面接的是聚合函数,比如sum,count,min,max之类就可以,但是row_number是排序函数,over中不加order by则Oracle无法知道排序规则所以报错了。