我想,加上order by ... desc 子句就可以了 select * from (select * from table order by col desc) where rownum<11
select * from (select * from table order by 时间字段/序列字段 desc) a where rownum<11
(select * from 表名 where rownum < (select count(*) + 1 from 表名)) minus (select * from 表名 where rownum < (select count(*) - 9 from 表名)) 这样就可以取出来最新的10条记录了!
select * from (select * from table order by col desc) where rownum<11 is ok
to leexhwhy(南天一剑) :你的SQL语句是不是有错误啊!不能用order by啊!请先测试后,在传SQL啊!
to 楼上: 我写的语句和leexhwhy(南天一剑)是一样的,可以运行. 给你个测试结果: SQL> desc worddefine; IWORDID NOT NULL NUMBER(8) CWORDTEXT NOT NULL VARCHAR2(200) CSYMBOL VARCHAR2(100) CWORDREF VARCHAR2(1024) SQL> select count(*) from worddefine; COUNT(*) ---------- 151124
接上面: select iWORDID from (select * from worddefine order by iWORDID desc) where rownum<11 IWORDID ------- 151124 151123 151122 151121 151120 151119 151118 151117 151116 151115
to leexhwhy(南天一剑) :呵呵,我先开始是在ORACLE 7.3.4中运行的,所以有错误啊! 在8I和9I可以运行啊,呵呵!
你的oracle 版本太低了 可以使用 select * from (select * from table group by col desc) where rownum<11
经过测试,楼上SQL所耗时间大于 (select * from 表名 where rownum < (select count(*) + 1 from 表名)) minus (select * from 表名 where rownum < (select count(*) - 9 from 表名)) ,所以还是按我的SQL运行比较好,呵呵!
to yansongonline(小嵩在线) 我不知道你是怎么测试的,你的语句执行效率很低,你可以 看看查询计划.
你的查询很明显要做两次全表扫描,怎么可能效率高呢???
select a.* from (select * from table_name order by rowid desc) a where rownum<11
select * from (select * from students order by rownum desc) where rownum<5
select * from (select * from table_name order by rownum desc) where rownum<11
to yansongonline(小嵩在线),还是公平点说话,你的方案根本不敢实施,你知道你的这种全表扫描的时间是多少,1分零7秒,我自己的一个比较大的表,所以我还是觉得如下实现比较好一点 select * from (select * from table order by rowid desc) where rownum<11
to leecooper0918(PajeroFans) and yujiabian(流氓兔子雨):不好意思,我先开始测试用的是一张小表,我现在又用了一张4万条记录的大表,果然速度不一样!支持你们的SQL,俺的那个SQL还是效率低了点啊!呵呵!
呵呵,试试 select * from (select * from table_name order by rownum desc) where rownum<11 吧! 刚才我还以为写错了呢!所以我又测试了一遍,没问题的。 因为rownum实际的记录返回值跟序列差不多。 请看下面的测试: SQL> desc rooms; Name Type Nullable Default Comments ------------ ------------ -------- ------- -------- ROOM_ID NUMBER(5) Y BUILDING VARCHAR2(15) Y ROOM_NUMBER NUMBER(4) Y NUMBER_SEATS NUMBER(4) Y DESCRIPTION VARCHAR2(50) Y SQL> select * from rooms;ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION ------- --------------- ----------- ------------ -------------------------------------------------- 99999 Building 7 310 1000 Large Lecture Hall 99998 Building 6 101 500 Small Lecture Hall 99997 Building 6 150 50 Discussion Room A 99996 Building 6 160 50 Discussion Room B 99995 Building 6 170 50 Discussion Room C 99994 Music Building 100 10 Music Practice Room 99993 Music Building 200 1000 Concert Room 99992 Building 7 300 75 Discussion Room D 99991 Building 7 310 50 Discussion Room E9 rows selectedSQL> select rownum,aa.* from rooms aa; ROWNUM ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION ---------- ------- --------------- ----------- ------------ -------------------------------------------------- 1 99999 Building 7 310 1000 Large Lecture Hall 2 99998 Building 6 101 500 Small Lecture Hall 3 99997 Building 6 150 50 Discussion Room A 4 99996 Building 6 160 50 Discussion Room B 5 99995 Building 6 170 50 Discussion Room C 6 99994 Music Building 100 10 Music Practice Room 7 99993 Music Building 200 1000 Concert Room 8 99992 Building 7 300 75 Discussion Room D 9 99991 Building 7 310 50 Discussion Room E9 rows selectedSQL> desc students; Name Type Nullable Default Comments --------------- ------------ -------- ------- -------- ID NUMBER(5) Y FIRST_NAME VARCHAR2(20) Y LAST_NAME VARCHAR2(20) Y MAJOR VARCHAR2(30) Y CURRENT_CREDITS NUMBER(3) Y SQL> select * from students; ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS ------ -------------------- -------------------- ------------------------------ --------------- 10000 Scott Smith Computer Science 0 10003 Margaret Mason History 0 10004 Joanne Junebug Computer Science 0 10005 Manish Murgratroid Economics 0 10006 Patrick Poll History 0 10007 Timothy Taller History 0 10008 Barbara Blues Economics 0 10009 David Dinsmore Music 0 10010 Ester Elegant Nutrition 0 10011 Rose Riznit Music 0 10012 Rita Razmataz Nutrition 011 rows selectedSQL> select rownum,aa.* from students aa; ROWNUM ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS ---------- ------ -------------------- -------------------- ------------------------------ --------------- 1 10000 Scott Smith Computer Science 0 2 10003 Margaret Mason History 0 3 10004 Joanne Junebug Computer Science 0 4 10005 Manish Murgratroid Economics 0 5 10006 Patrick Poll History 0 6 10007 Timothy Taller History 0 7 10008 Barbara Blues Economics 0 8 10009 David Dinsmore Music 0 9 10010 Ester Elegant Nutrition 0 10 10011 Rose Riznit Music 0 11 10012 Rita Razmataz Nutrition 011 rows selected
where rownum<11
where rownum<11
minus
(select * from 表名 where rownum < (select count(*) - 9 from 表名))
这样就可以取出来最新的10条记录了!
where rownum<11
is ok
我写的语句和leexhwhy(南天一剑)是一样的,可以运行.
给你个测试结果: SQL> desc worddefine;
IWORDID NOT NULL NUMBER(8)
CWORDTEXT NOT NULL VARCHAR2(200)
CSYMBOL VARCHAR2(100)
CWORDREF VARCHAR2(1024) SQL> select count(*) from worddefine;
COUNT(*)
----------
151124
select iWORDID from (select * from worddefine order by iWORDID desc)
where rownum<11 IWORDID
-------
151124
151123
151122
151121
151120
151119
151118
151117
151116
151115
在8I和9I可以运行啊,呵呵!
select * from (select * from table group by col desc)
where rownum<11
(select * from 表名 where rownum < (select count(*) + 1 from 表名))
minus
(select * from 表名 where rownum < (select count(*) - 9 from 表名))
,所以还是按我的SQL运行比较好,呵呵!
我不知道你是怎么测试的,你的语句执行效率很低,你可以
看看查询计划.
你的查询很明显要做两次全表扫描,怎么可能效率高呢???
select * from (select * from table order by rowid desc)
where rownum<11
select * from
(select * from table_name order by rownum desc)
where rownum<11 吧!
刚才我还以为写错了呢!所以我又测试了一遍,没问题的。
因为rownum实际的记录返回值跟序列差不多。
请看下面的测试:
SQL> desc rooms;
Name Type Nullable Default Comments
------------ ------------ -------- ------- --------
ROOM_ID NUMBER(5) Y
BUILDING VARCHAR2(15) Y
ROOM_NUMBER NUMBER(4) Y
NUMBER_SEATS NUMBER(4) Y
DESCRIPTION VARCHAR2(50) Y SQL> select * from rooms;ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION
------- --------------- ----------- ------------ --------------------------------------------------
99999 Building 7 310 1000 Large Lecture Hall
99998 Building 6 101 500 Small Lecture Hall
99997 Building 6 150 50 Discussion Room A
99996 Building 6 160 50 Discussion Room B
99995 Building 6 170 50 Discussion Room C
99994 Music Building 100 10 Music Practice Room
99993 Music Building 200 1000 Concert Room
99992 Building 7 300 75 Discussion Room D
99991 Building 7 310 50 Discussion Room E9 rows selectedSQL> select rownum,aa.* from rooms aa; ROWNUM ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION
---------- ------- --------------- ----------- ------------ --------------------------------------------------
1 99999 Building 7 310 1000 Large Lecture Hall
2 99998 Building 6 101 500 Small Lecture Hall
3 99997 Building 6 150 50 Discussion Room A
4 99996 Building 6 160 50 Discussion Room B
5 99995 Building 6 170 50 Discussion Room C
6 99994 Music Building 100 10 Music Practice Room
7 99993 Music Building 200 1000 Concert Room
8 99992 Building 7 300 75 Discussion Room D
9 99991 Building 7 310 50 Discussion Room E9 rows selectedSQL> desc students;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
ID NUMBER(5) Y
FIRST_NAME VARCHAR2(20) Y
LAST_NAME VARCHAR2(20) Y
MAJOR VARCHAR2(30) Y
CURRENT_CREDITS NUMBER(3) Y SQL> select * from students; ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
------ -------------------- -------------------- ------------------------------ ---------------
10000 Scott Smith Computer Science 0
10003 Margaret Mason History 0
10004 Joanne Junebug Computer Science 0
10005 Manish Murgratroid Economics 0
10006 Patrick Poll History 0
10007 Timothy Taller History 0
10008 Barbara Blues Economics 0
10009 David Dinsmore Music 0
10010 Ester Elegant Nutrition 0
10011 Rose Riznit Music 0
10012 Rita Razmataz Nutrition 011 rows selectedSQL> select rownum,aa.* from students aa; ROWNUM ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
---------- ------ -------------------- -------------------- ------------------------------ ---------------
1 10000 Scott Smith Computer Science 0
2 10003 Margaret Mason History 0
3 10004 Joanne Junebug Computer Science 0
4 10005 Manish Murgratroid Economics 0
5 10006 Patrick Poll History 0
6 10007 Timothy Taller History 0
7 10008 Barbara Blues Economics 0
8 10009 David Dinsmore Music 0
9 10010 Ester Elegant Nutrition 0
10 10011 Rose Riznit Music 0
11 10012 Rita Razmataz Nutrition 011 rows selected