create table TEST2(A VARCHAR2(10),B VARCHAR(5),C VARCHAR2(10)); INSERT INTO TEST2 VALUES('a01','b01','xx'); INSERT INTO TEST2 VALUES('a01','b02','xx'); INSERT INTO TEST2 VALUES('a02','b01','xx'); INSERT INTO TEST2 VALUES('a03','b01','xx');create or replace procedure updateTest2ColB is m_a varchar2(10); m_cnt number(10); cursor cu is select a,b,c from test2 for update; c cu%ROWTYPE; begin m_a:=' '; m_cnt:=0; open cu;
LOOP FETCH cu into c; EXIT WHEN cu%NOTFOUND; -- process the data
if m_a<>c.a then m_cnt:=1; m_a:=c.a; else m_cnt:=m_cnt+1; end if; update test2 set b=m_cnt where CURRENT OF cu; END LOOP; commit; close cu; end; /exec updatetest2colb;select * from test2;
哈, beckhambobo的方法,最好了。row_number() over(partition by )这个用法记下来了。
SQL> Update t_tempplmx 2 Set mx_bh = (select row_number() over(partition by pl_bh Order By pl_bh) 3 From t_tempplmx where t_tempplmx.rowid = t_tempplmx.rowid Order By pl_bh) 4 ; From t_tempplmx where t_tempplmx.rowid = t_tempplmx.rowid Order By pl_bh) * ERROR 位于第 3 行: ORA-00907: 缺少右括号这是哪里不对啊?
Update t_tempplmx aa Set mx_bh = (select row_number() over(partition by pl_bh) From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
SQL> Update t_tempplmx aa 2 Set mx_bh = (select row_number() over(partition by pl_bh) 3 From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh 4 ; Set mx_bh = (select row_number() over(partition by pl_bh) * ERROR 位于第 2 行: ORA-30485: 在窗口说明中丢失 ORDER BY 表达式怎么回事啊? 多谢!
我这里也遇到了错误! update test aa set b = (select row_number() over(partition by a) from test where rowid=aa.rowid) order by a,c;set b = (select row_number() over(partition by a) * 错误位于第2行: ORA-00923: 未找到预期 FROM 关键字这跟oracle 的版本有关系吗?
sorry,试试这个。 Update t_tempplmx aa Set mx_bh = (select row_number() over(partition by pl_bh order by pl_bh) From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
还是有问题啊。SQL> Update t_tempplmx aa 2 Set mx_bh = (select row_number() over(partition by pl_bh order by pl_bh) 3 From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh 4 ; From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh * ERROR 位于第 3 行: ORA-00933: SQL 命令未正确结束
create table TEST2(A VARCHAR2(10),B VARCHAR(5),C VARCHAR2(10)); INSERT INTO TEST2 VALUES('a01','b01','xx'); INSERT INTO TEST2 VALUES('a01','b02','xx'); INSERT INTO TEST2 VALUES('a02','b01','xx'); INSERT INTO TEST2 VALUES('a03','b01','xx');SQL> select * from test2;A B C ---------- ----- ---------- a01 b01 xx a01 b02 xx a02 b01 xx a03 b01 xxSQL> update test2 aa set b=(select row_number() over(partition by a order by a) from test2 where rowid=aa.rowid);4 rows updatedSQL> select * from test2;A B C ---------- ----- ---------- a01 1 xx a01 1 xx a02 1 xx a03 1 xx从以上方法来看,顺序已打乱了,或者楼主只作查询用比较容易实现。如: SQL> select a,row_number() over(partition by a order by a) b,c from test2;A B C ---------- ---------- ---------- a01 1 xx a01 2 xx a02 1 xx a03 1 xx若要更新,那清空原表记录. insert into test1 select a,row_number() over(partition by a order by a) b,c from test2;
update yourtable tab1 set tab1.b=( select count(*) from yourtable tab2 where tab1.a=tab2.a and tab1.rowid>=tab2.rowid);
受Lastdrop(空杯)启发,改进如下 update yourtable set b=(select max(nvl(rownum,0)) from yourtable t where t.a=tt.a and tt.rowid>=t.rowid);
INSERT INTO TEST2 VALUES('a01','b01','xx');
INSERT INTO TEST2 VALUES('a01','b02','xx');
INSERT INTO TEST2 VALUES('a02','b01','xx');
INSERT INTO TEST2 VALUES('a03','b01','xx');create or replace procedure updateTest2ColB
is
m_a varchar2(10);
m_cnt number(10);
cursor cu is select a,b,c from test2 for update;
c cu%ROWTYPE;
begin
m_a:=' ';
m_cnt:=0;
open cu;
LOOP
FETCH cu into c;
EXIT WHEN cu%NOTFOUND; -- process the data
if m_a<>c.a then
m_cnt:=1;
m_a:=c.a;
else
m_cnt:=m_cnt+1;
end if;
update test2 set b=m_cnt where CURRENT OF cu;
END LOOP;
commit;
close cu;
end;
/exec updatetest2colb;select * from test2;
2 Set mx_bh = (select row_number() over(partition by pl_bh Order By pl_bh)
3 From t_tempplmx where t_tempplmx.rowid = t_tempplmx.rowid Order By pl_bh)
4 ;
From t_tempplmx where t_tempplmx.rowid = t_tempplmx.rowid Order By pl_bh)
*
ERROR 位于第 3 行:
ORA-00907: 缺少右括号这是哪里不对啊?
Set mx_bh = (select row_number() over(partition by pl_bh)
From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
2 Set mx_bh = (select row_number() over(partition by pl_bh)
3 From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
4 ;
Set mx_bh = (select row_number() over(partition by pl_bh)
*
ERROR 位于第 2 行:
ORA-30485: 在窗口说明中丢失 ORDER BY 表达式怎么回事啊? 多谢!
set b = (select row_number() over(partition by a)
from test where rowid=aa.rowid) order by a,c;set b = (select row_number() over(partition by a)
*
错误位于第2行:
ORA-00923: 未找到预期 FROM 关键字这跟oracle 的版本有关系吗?
Update t_tempplmx aa
Set mx_bh = (select row_number() over(partition by pl_bh order by pl_bh)
From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
2 Set mx_bh = (select row_number() over(partition by pl_bh order by pl_bh)
3 From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
4 ;
From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
*
ERROR 位于第 3 行:
ORA-00933: SQL 命令未正确结束
INSERT INTO TEST2 VALUES('a01','b01','xx');
INSERT INTO TEST2 VALUES('a01','b02','xx');
INSERT INTO TEST2 VALUES('a02','b01','xx');
INSERT INTO TEST2 VALUES('a03','b01','xx');SQL> select * from test2;A B C
---------- ----- ----------
a01 b01 xx
a01 b02 xx
a02 b01 xx
a03 b01 xxSQL> update test2 aa set b=(select row_number() over(partition by a order by a) from test2 where rowid=aa.rowid);4 rows updatedSQL> select * from test2;A B C
---------- ----- ----------
a01 1 xx
a01 1 xx
a02 1 xx
a03 1 xx从以上方法来看,顺序已打乱了,或者楼主只作查询用比较容易实现。如:
SQL> select a,row_number() over(partition by a order by a) b,c from test2;A B C
---------- ---------- ----------
a01 1 xx
a01 2 xx
a02 1 xx
a03 1 xx若要更新,那清空原表记录.
insert into test1 select a,row_number() over(partition by a order by a) b,c from test2;
where tab1.a=tab2.a and tab1.rowid>=tab2.rowid);
update yourtable set b=(select max(nvl(rownum,0)) from yourtable t where t.a=tt.a and tt.rowid>=t.rowid);
tt.rowid>=t.rowid
你敢说rowid最后的比前一条记录大?
但对于此问题,用rowid就不能保证所得到的顺序.