SQL中如何从已排序的结果集中取出前面第5到第10条 (50分)

T

top05

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #1

补充：
在ORACLE 中：
select top 10 * from table_name order by one;错误!
rownum也只能取出未排序前的前10条

 

热

热血

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #2

limit

 

吴

吴剑明

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #3

如果是SQL SERVER：
select top 10 * from aa order by X

 

千

千中元

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #4

top 10

 

T

top05

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #5

补充：
在ORACLE 中：
select * from table_name order by one;

 

千

千中元

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #6

这里没装oracle,不过应该可以top10,如果没记错的话。
BTW:你试了不行么？

 

F

foolaliu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #7

这样试试
set rowcount 10
select * from table_name order by one
set rowcount 0

 

H

hxchxc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #8

select top 10 * from table_name order by one
这个我在sql 7.0中是可以的

 

W

wind2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #9

oracle中有伪列这一字段可以加以利用。
select 字段1,字段2,....字段n,rownum form table_name where rownum<=10 order by 字段1;
在wind98+ora7下实验通过

 

M

mr_mist

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #10

SELECT * FROM
( SELECT * FROM TABLW_NAME ORDER BY ONE DESC)
WHERE ROWNUM<=10

 

G

gongjan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #11

select
* from sys_menu
where rownum <= 10
order by group_id;
Oracle72 下通过，借花献佛，试一试

 

T

TSQFZY

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #12

select top 10 * from table_name order by one
在SQL SERVER 7中可以嘛。我常用。

 

H

Hunj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #13

记得好象是这样写
select * from
(select * from tablename order by one)
where rownum >= 5 and rownum <= 10;

 

H

Hunj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-21

• #14

记得好象是这样写
select * from
(select rownum,* from tablename order by one)
where rownum >= 5 and rownum <= 10;

 

Y

yaojian

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-23

• #15

In oracle, rownum is number BEFORE order.
ANd SQL92do
es not support it.
Try use cursor:
declare a cursor and then
fetch top 10. (Static SQL)
In Java, Just create a resultset and next() 5 times and fetcch another 5 times and close it. It equals cursor. (it is cursor actually.)

 

G

galaxy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-26

• #16

去Chinaasp的sql论坛看看

 

S

sportsman

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-08-28

• #17

select * from table
where column1 in (select top 10 column1 from table)
and column1 not in (select top 5 column1 from table)
column1 is distinct
haha!

 

E

eguy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-03

• #18

top05:请继续或结束问题

 

A

arm

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-06

• #19

SELECT * FROM YOURTABLE
WHERE ROWNUM<10
MINUS
SELECT * FROM YOURTABLE
WHERE ROWNUM<5

 

D

daiqingbo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-06

• #20

我看sportsman的回答可行