今天一个网友的问题：
create table T
(
ID NUMBER,
NAME VARCHAR2(20),
ADDR VARCHAR2(20),
INSERTDATA DATE
);
insert into T (ID, NAME, ADDR, INSERTDATA)
values (1, '张', '北京', to_date('09-12-2006 10:07:45', 'dd-mm-yyyy hh24:mi:ss'));
insert into T (ID, NAME, ADDR, INSERTDATA)
values (3, '张', '北京', to_date('14-12-2006 10:08:05', 'dd-mm-yyyy hh24:mi:ss'));
insert into T (ID, NAME, ADDR, INSERTDATA)
values (2, '张', '北京', to_date('19-12-2006 10:08:42', 'dd-mm-yyyy hh24:mi:ss'));
insert into T (ID, NAME, ADDR, INSERTDATA)
values (4, '王', '上海', to_date('19-12-2006 10:09:33', 'dd-mm-yyyy hh24:mi:ss'));
insert into T (ID, NAME, ADDR, INSERTDATA)
values (5, '王', '上海', to_date('16-12-2006 10:10:15', 'dd-mm-yyyy hh24:mi:ss'));
commit;
我想取T这个表中姓名+地址相同,并且时间是最新的一条记录，有没有办法取出来？
答复：
SQL> select name,addr,rq from (select name,addr,to_char(insertdata,'yyyymmdd hh24:mi:ss') rq,rank() over(partition by name,addr order by insertdata desc) rn from t) where rn=1;
NAME ADDR RQ
-------------------- -------------------- -----------------
王 上海 20061219 10:09:33
张 北京 20061219 10:08:42
/*************************************************************************************/
近期问和重复行相关问题的还是很多，顺被把blog里的总结转一下：
http://zhouwf0726.itpub.net/post/9689/230599
create table students
(id number(15,0),
area varchar2(10),
stu_type varchar2(2),
score number(20,2));
create index idx_students on students(id);
insert into students values(1, '111', 'g', 80 );
insert into students values(1, '111', 'j', 80 );
insert into students values(1, '222', 'g', 89 );
insert into students values(1, '222', 'g', 68 );
insert into students values(2, '111', 'g', 80 );
insert into students values(2, '111', 'j', 70 );
insert into students values(2, '222', 'g', 60 );
insert into students values(2, '222', 'j', 65 );
insert into students values(3, '111', 'g', 75 );
insert into students values(3, '111', 'j', 58 );
insert into students values(3, '222', 'g', 58 );
insert into students values(3, '222', 'j', 90 );
insert into students values(4, '111', 'g', 89 );
insert into students values(4, '111', 'j', 90 );
insert into students values(4, '222', 'g', 90 );
insert into students values(4, '222', 'j', 89 );
commit;
SQL> delete from students a where rowid>(select min(rowid) from students b where a.id=b.id);
已删除12行。
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 3186961758
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 50 | 4 (25)| 00:00:01 |
| 1 | DELETE | STUDENTS | | | | |
| 2 | NESTED LOOPS | | 1 | 50 | 4 (25)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 4 | 100 | 4 (25)| 00:00:01 |
| 4 | SORT GROUP BY | | 4 | 100 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| STUDENTS | 4 | 100 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_STUDENTS | 1 | 25 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."ID"="ID"
filter(ROWID>"VW_COL_1"
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
787 recursive calls
16 db block gets
208 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL*Net to client
625 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
12 rows processed
SQL> rollback;
回退已完成。
已用时间: 00: 00: 00.01
SQL> alter system flush shared_pool;
系统已更改。
已用时间: 00: 00: 00.03
SQL> delete from students where rowid = (select rowid from (select rowid,row_number() over(partition by id order by id) rn from students) temp where students.rowid=temp.rowid and temp.rn>1);
已删除12行。
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 731036090
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 25 | 11 (19)| 00:00:01 |
| 1 | DELETE | STUDENTS | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | STUDENTS | 4 | 100 | 3 (0)| 00:00:01 |
|* 4 | VIEW | | 4 | 100 | 4 (25)| 00:00:01 |
| 5 | WINDOW SORT | | 4 | 100 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL| STUDENTS | 4 | 100 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWID= (SELECT ROWID FROM (SELECT ROWID
"ROWID",ROW_NUMBER() OVER ( PARTITION BY "ID" ORDER BY "ID"
"RN",ROWID,ROWID FROM "STUDENTS" "STUDENTS"
"TEMP" WHERE
"TEMP".ROWID=:B1 AND "TEMP"."RN">1))
4 - filter("TEMP".ROWID=:B1 AND "TEMP"."RN">1)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
381 recursive calls
38 db block gets
188 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
724 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
23 sorts (memory)
0 sorts (disk)
12 rows processed
SQL> rollback;
回退已完成。
已用时间: 00: 00: 00.03
SQL> alter system flush shared_pool;
系统已更改。
已用时间: 00: 00: 00.03
SQL> delete from students a where rowid>any (select rowid from students b where a.id=b.id);
已删除12行。
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 1252538939
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 50 | 1 (0)| 00:00:01 |
| 1 | DELETE | STUDENTS | | | | |
| 2 | NESTED LOOPS SEMI| | 1 | 50 | 1 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_STUDENTS | 4 | 100 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IDX_STUDENTS | 1 | 25 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID"
filter(ROWID>ROWID AND ROWID
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
381 recursive calls
16 db block gets
72 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
12 rows processed
SQL> rollback;
回退已完成。
已用时间: 00: 00: 00.03
SQL> alter system flush shared_pool;
系统已更改。
已用时间: 00: 00: 00.03
SQL>
SQL> delete students where rowid in (
2 select rid from
3 (select rowid rid,row_number() over(partition by id order by id) rn from students)
4 where rn > 1) ;
已删除12行。
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 3190561995
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 37 | 6 (34)| 00:00:01 |
| 1 | DELETE | STUDENTS | | | | |
| 2 | NESTED LOOPS | | 1 | 37 | 6 (34)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 4 | 48 | 4 (25)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 100 | | |
|* 5 | VIEW | | 4 | 100 | 4 (25)| 00:00:01 |
| 6 | WINDOW SORT | | 4 | 100 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | STUDENTS | 4 | 100 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY USER ROWID| STUDENTS | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("RN">1)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
380 recursive calls
38 db block gets
89 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
686 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
12 rows processed
关于分析函数可以参考以下地址：
http://www.itpub.net/showthread.php?s=&threadid=608107
