试试
C:\Documents and Settings\zero>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 12月 28 12:33:52 2007
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> conn test/1
已连接。
SQL> drop table NTEST cascade constraints;
表已丢弃。
SQL> create table NTEST
2(
3A VARCHAR2(20),
4B VARCHAR2(20),
5C VARCHAR2(20)
6);
表已创建。
SQL>
SQL> alter table NTEST disable all triggers;
表已更改。
SQL> insert into NTEST (A, B, C)
2values ('10c', '2', '33');
已创建 1 行。
SQL> insert into NTEST (A, B, C)
2values ('10c', '2', '37');
已创建 1 行。
SQL> insert into NTEST (A, B, C)
2values ('10c', '5', '41');
已创建 1 行。
SQL> insert into NTEST (A, B, C)
2values ('10c', '5', '45');
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter table NTEST enable all triggers;
表已更改。
SQL>
SQL> col a for a10
SQL> col b for a10
SQL> col c for a10
SQL>
SQL> Selecta, to_char((Select Max(b) From ntest)+rn1) b,c From
2(Select a,b,c, row_number() over( Order By a,b) rn1 From (Select a,b,cFrom (Select a,b,c, row_number() over(parti
tion By a,b Order By a,b) rn From ntest )Where rn>1))
3Union
4Select a,b,c From (Select a,b,c, row_number() over(partition By a,b Order By a,b) rn From ntest )Where rn=1
5/
A
B
C
---------- ---------- ----------
10c2
33
10c5
41
10c6
37
10c7
45
SQL>
|