最新更新主题

月度关注热点

关于oracle的order by排序问题

字体: 小 中 大 | 打印 发表于: 2008-8-12 09:42    作者: arsenal_cd    来源: CNOUG博客首页

select * from SERVERLIST where rownum<=30 order by id desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>30 and no<=60 order by no desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>60 and no<=90 order by no desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>90 and no<=120 order by no desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>120 and no<=150 order by no desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>150 and no<=180 order by no desc;



select * from SERVERLIST where rownum<=30 order by id desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>30 and no<=60 order by id desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>60 and no<=90 order by id desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>90 and no<=120 order by id desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>120 and no<=150 order by id desc;
select * from(select rownum no,ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where no>150 and no<=180 order by id desc;



上面两种方法
结果集都会出现交叉
为什么会这样???
假如一共180条(或180以内,17X条)记录
我想每次取30条
怎么做?
我也来说两句 查看全部回复

最新回复

  • nemo410 (2008-8-12 09:48:54)

    什么版本的數據庫哦﹐order by 能內置﹐或者是sql 工具的問題﹐ pl/sql 5.0.0 好像不行哦﹗

  • arsenal_cd (2008-8-12 09:50:24)

    数据库是oracle 10g2
    我用sql*plus worksheet执行的

  • 123ccf (2008-8-12 16:00:07)

    后面那个ORDER BY 去掉看看。。

  • arsenal_cd (2008-8-12 16:09:13)

    去掉一样的不行啊
    咋回事啊?

  • 123ccf (2008-8-12 16:17:21)

    我知道了。。。

  • 123ccf (2008-8-12 16:19:30)

    select * from(select ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc) where rownum>30 and rownum<=60 order by id desc;
    估计要这么些。。实在不行，就先order by rownum试试

  • arsenal_cd (2008-8-12 16:28:39)

    楼上
    你自己执行过没?
    ROWNUM能这么用吗??

  • arsenal_cd (2008-8-12 16:38:26)

    真是奇怪了
    我在另外一台备份的服务器上执行1楼的SQL
    排序一点问题没有
    难道是ORACLE的设置什么没对吗?

  • genomics (2008-8-12 16:48:36)

    select * from (select rownum no,a.*  from (select ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc,rowid) a  where no<=60) where  no>30 order by no ;

  • arsenal_cd (2008-8-12 17:06:52)

    上面的执行有错误啊

    a  where no<=60
             *
    第 3 行出现错误:
    ORA-00904: "NO": invalid identifier

  • genomics (2008-8-13 10:16:11)

    select * from (select rownum rn,a.*  from (select ID,AREANAME,SERVERIP,PASSWD,PORT from SERVERLIST order by id desc,rowid) a  where a.rn<=60) b where  b.rn>30 order by b.rn ;
查看全部回复 我也来说两句