如何控制query返回的记录数目？(100分)

侠

侠圣

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-13

• #1

假设一个query返回100条记录（经过排序），如何取出其中部分记录，如1到10，或者30－50
等。后台数据库是oracle。另外下面这条sql语句错在什么地方？
select * from (select pgm_id,count(*) as times from vod_rec group by pgm_id order by times desc)
where rownum<11
写成（不加排序）
select * from (select pgm_id,count(*) as times from vod_rec group by pgm_id)
where rownum<11
就对了
我得目的是要实现返回排序后记录中的第m名到第n名

 

P

pxlei

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-13

• #2

oracle好象不能支持从m到n的方法(或者我不知道),不过,你可以
通过检索前n,前m条记录,之后在相减.
通过union test

 

W

wumeng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-13

• #3

建一汇总ＴＡＢＬＥ。查询前先汇总。

 

G

g622

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-13

• #4

rownum如果是连续唯一的，可以rownum>n and rownum<m
如果不是，只有先把结果放到本地的临时表里了，一个一个取记录

 

W

wangxd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-13

• #5

用视图吧，较好的解决办法了
create view temp as select pgm_id,count(*) as times from vod_rec group by pgm_id order by times desc
select * from temp where rownum<11
drop view temp

 

程

程云

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-13

• #6

假如m=3,n=10。

Select top 10-3 *
from(
select top 10 * from (select pgm_id,count(*) as times from vod_rec group by pgm_id order by times desc)
where rownum<11
order by times desc
)
order by times

大体如此，
我还没试过，理论上可行，你就安这个思路我试试，
如有疑问再联系。

 

Z

zwhc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-13

• #7

我前两天刚问过这问题
http://www.delphibbs.com/delphibbs/DispQ.asp?LID=359330

 

B

BaKuBaKu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-14

• #8

SQL Server 可以加入范围子句：
Select Top 10 * From ...

 

A

arm

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-14

• #9

11-20
select * from (select pgm_id,count(*) as times from vod_rec group by pgm_id)
where rownum<21
union all
select * from (select pgm_id,count(*) as times from vod_rec group by pgm_id)
where rownum<11

 

A

arm

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-14

• #10

不好意思，错了
11-20
select * from (select pgm_id,count(*) as times from vod_rec group by pgm_id)
where rownum<21
minus
select * from (select pgm_id,count(*) as times from vod_rec group by pgm_id)
where rownum<11

 

H

hsj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-18

• #11

后一问题的错误在于Order by后的字段是找不到的，您可以直接用Count(*)来代替.

 

W

wjiachun

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-11-19

• #12

多人接受答案了。