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
这样的语句改咋写啊?谢谢各位了
11 个解决方案
#1
update a t set seq=(select rn from
select a.*,row_number()over(partition by parent)rn from a)
where name=t.name)
select a.*,row_number()over(partition by parent)rn from a)
where name=t.name)
#2
SQL> select * from a;
N SEQ PAR
- ---------- ---
A 0 001
B 0 001
C 0 001
D 0 001
E 0 002
F 0 002
6 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 002
6 rows selected.
#3
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
6 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 002
6 rows selected.
#4
update 表名 tb
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);
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);
#5
问个问题哦
刚才我在试的过程中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
#6
如果有NAME相同的记录的话那么需要把上面的语句改进一下
加一个条件x.parent=test1.parent
加一个条件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 002
7 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 002
7 rows selected.
#7
UPDATE T SET SEQ = ROW_NUMBER() OVER(ORDER BY PARENT, NAME)
没测试过, 你可以调试一下。最好把创建脚本和插入数据的脚本发上来,这样才容易帮你测试。
没测试过, 你可以调试一下。最好把创建脚本和插入数据的脚本发上来,这样才容易帮你测试。
#8
UPDATE T SET SEQ = ROW_NUMBER() OVER(PARTITION BY PARENT ORDER BY NAME)
^_^
^_^
#9
update 表名 tb
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);
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);
#10
因为partition by是分组,相当于group by,所以如果over前面接的是聚合函数,比如sum,count,min,max之类就可以,但是row_number是排序函数,over中不加order by则Oracle无法知道排序规则所以报错了。
#11
呵呵 谢谢解答啊
有些日子没看到你了
有些日子没看到你了
#1
update a t set seq=(select rn from
select a.*,row_number()over(partition by parent)rn from a)
where name=t.name)
select a.*,row_number()over(partition by parent)rn from a)
where name=t.name)
#2
SQL> select * from a;
N SEQ PAR
- ---------- ---
A 0 001
B 0 001
C 0 001
D 0 001
E 0 002
F 0 002
6 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 002
6 rows selected.
#3
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
6 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 002
6 rows selected.
#4
update 表名 tb
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);
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);
#5
问个问题哦
刚才我在试的过程中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
#6
如果有NAME相同的记录的话那么需要把上面的语句改进一下
加一个条件x.parent=test1.parent
加一个条件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 002
7 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 002
7 rows selected.
#7
UPDATE T SET SEQ = ROW_NUMBER() OVER(ORDER BY PARENT, NAME)
没测试过, 你可以调试一下。最好把创建脚本和插入数据的脚本发上来,这样才容易帮你测试。
没测试过, 你可以调试一下。最好把创建脚本和插入数据的脚本发上来,这样才容易帮你测试。
#8
UPDATE T SET SEQ = ROW_NUMBER() OVER(PARTITION BY PARENT ORDER BY NAME)
^_^
^_^
#9
update 表名 tb
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);
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);
#10
因为partition by是分组,相当于group by,所以如果over前面接的是聚合函数,比如sum,count,min,max之类就可以,但是row_number是排序函数,over中不加order by则Oracle无法知道排序规则所以报错了。
#11
呵呵 谢谢解答啊
有些日子没看到你了
有些日子没看到你了