请帮忙看看这句SQL, 实现分页查询, 但目前发现排序有问题,请大家帮忙.谢谢

Question

SELECT     SENTMESSAGEID, to_char(SENTLOGTIME, 'YYYYMMDDHH24MISS'),MESSAGEID,MESSAGEGROUPID,LENGTH,
                    CAREFIELDNO1,CAREFIELDNO2,CAREFIELDNO3,BUFFERDATA || '#',TABLESAVED,
                    DESTINATIONCODE,SENDRESULT,RESENDNUMBER,SENTTIMETVALUE,MESSAGEPASSED,SENDTIME
        FROM (select ROWNUM as rn, SENTMESSAGEID,SENTLOGTIME,MESSAGEID,MESSAGEGROUPID,LENGTH,
                    CAREFIELDNO1,CAREFIELDNO2,CAREFIELDNO3,BUFFERDATA,TABLESAVED,
                    DESTINATIONCODE,SENDRESULT,RESENDNUMBER,SENTTIMETVALUE,MESSAGEPASSED,SENDTIME
            FROM     xcom.SENTMESSAGEBUFFERLOG  
            WHERE 
                        MESSAGEID = 'SM01'
            AND        TO_CHAR(SENTLOGTIME, 'YYYYMMDD') BETWEEN
                        NVL(TRIM('20010101'),TO_CHAR(SENTLOGTIME, 'YYYYMMDD'))
                        AND
                        NVL(TRIM('20120101'),TO_CHAR(SENTLOGTIME, 'YYYYMMDD')) 
            order by sentmessageid DESC)     TB 
            
where TB.rn between 1 and 20  order by sentmessageid DESC;
按理说, 第一页查到的记录,  sentmessageid 字段值应该是最大的, 但实际并不是这样, 而且排序很乱.自己没看出问题, 请大家帮忙 谢谢

Answer 1

-- 直接 用 row_number()函数：SELECT SENTMESSAGEID, to_char(SENTLOGTIME, 'YYYYMMDDHH24MISS'), MESSAGEID, MESSAGEGROUPID, LENGTH,
       CAREFIELDNO1, CAREFIELDNO2, CAREFIELDNO3, BUFFERDATA ||'#', TABLESAVED,
       DESTINATIONCODE, SENDRESULT, RESENDNUMBER, SENTTIMETVALUE, MESSAGEPASSED, SENDTIME
 FROM (select ROW_NUMBER() OVER(order by sentmessageid DESC) as rn, 
              SENTMESSAGEID,
              SENTLOGTIME,
              MESSAGEID,
              MESSAGEGROUPID,
              LENGTH,
              CAREFIELDNO1,
              CAREFIELDNO2,
              CAREFIELDNO3,
              BUFFERDATA,
              TABLESAVED,
              DESTINATIONCODE,SENDRESULT,RESENDNUMBER,SENTTIMETVALUE,MESSAGEPASSED,SENDTIME
         FROM xcom.SENTMESSAGEBUFFERLOG  
        WHERE MESSAGEID = 'SM01'
          AND TO_CHAR(SENTLOGTIME, 'YYYYMMDD') BETWEEN NVL(TRIM('20010101'),TO_CHAR(SENTLOGTIME,'YYYYMMDD'))
          AND NVL(TRIM('20120101'),TO_CHAR(SENTLOGTIME, 'YYYYMMDD')) ) TB 
where TB.rn between 1 and 20  order by sentmessageid DESC;-- 上面的语句写的很糟糕，日期字段where条件的比较，没你这样搞的!

Answer 2

SELECT SENTMESSAGEID, to_char(SENTLOGTIME, 'YYYYMMDDHH24MISS'), MESSAGEID, MESSAGEGROUPID, LENGTH,
       CAREFIELDNO1, CAREFIELDNO2, CAREFIELDNO3, BUFFERDATA ||'#', TABLESAVED,
       DESTINATIONCODE, SENDRESULT, RESENDNUMBER, SENTTIMETVALUE, MESSAGEPASSED, SENDTIME
 FROM (select ROW_NUMBER() OVER(order by sentmessageid DESC) as rn, 
              SENTMESSAGEID,
              SENTLOGTIME,
              MESSAGEID,
              MESSAGEGROUPID,
              LENGTH,
              CAREFIELDNO1,
              CAREFIELDNO2,
              CAREFIELDNO3,
              BUFFERDATA,
              TABLESAVED,
              DESTINATIONCODE,SENDRESULT,RESENDNUMBER,SENTTIMETVALUE,MESSAGEPASSED,SENDTIME
         FROM xcom.SENTMESSAGEBUFFERLOG  
        WHERE MESSAGEID = 'SM01'
          AND SENTLOGTIME >= to_date('20010101','yyyymmdd')
          AND SENTLOGTIME <= to_date('20120101','yyyymmdd') ) TB 
where TB.rn between 1 and 20  order by sentmessageid DESC;-- 日期字段的比较，一般是用 date_column >= ... and date_column < ...              (不要用 <= )
-- 具体请看：
http://topic.csdn.net/u/20110324/09/12bf9066-57c8-4cdf-8481-d9efad646c5c.html

Answer 3

-- 请看：SQL> select rownum, empno, ename, job, mgr, hiredate, sal, comm, deptno from emp order by sal DESC;    ROWNUM      EMPNO ENAME                JOB                       MGR HIREDATE              SAL    COMM        DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         9       7839 KING                 PRESIDENT                     17-11月-81           5000                10
        13       7902 FORD                 ANALYST                  7566 03-12月-81           3000                20
         8       7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                20
         4       7566 JONES                MANAGER                  7839 02-4月 -81           2975                20
         6       7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                30
         7       7782 CLARK                MANAGER                  7839 09-6月 -81           2450                10
         2       7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30
        10       7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30
        14       7934 MILLER               CLERK                    7782 23-1月 -82           1300                10
         3       7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30
         5       7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30
        11       7876 ADAMS                CLERK                    7788 23-5月 -87           1100                20
        12       7900 JAMES                CLERK                    7698 03-12月-81            950                30
         1       7369 SMITH                CLERK                    7902 17-12月-80            800                20已选择14行。-- 你会发现：工资最高的员工，其 rownum 并不等于1，
-- rownum 是行号，在你排序前就已经产生啦!
-- 所以：造成你的外层查询以为其 rownum 是根据排序产生的，其实不然!

Answer 4

SQL> select rownum, empno, ename, job, mgr, hiredate, sal, comm, deptno
  2    from (select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp order by sal DESC) t;    ROWNUM      EMPNO ENAME                JOB                       MGR HIREDATE              SAL    COMM        DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         1       7839 KING                 PRESIDENT                     17-11月-81           5000                10
         2       7902 FORD                 ANALYST                  7566 03-12月-81           3000                20
         3       7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                20
         4       7566 JONES                MANAGER                  7839 02-4月 -81           2975                20
         5       7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                30
         6       7782 CLARK                MANAGER                  7839 09-6月 -81           2450                10
         7       7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30
         8       7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30
         9       7934 MILLER               CLERK                    7782 23-1月 -82           1300                10
        10       7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30
        11       7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30
        12       7876 ADAMS                CLERK                    7788 23-5月 -87           1100                20
        13       7900 JAMES                CLERK                    7698 03-12月-81            950                30
        14       7369 SMITH                CLERK                    7902 17-12月-80            800                20已选择14行。-- 先在子层查询中排序，然后在外层查询中用 rownum ，就肯定正确啦! 
但是：此时还是不能直接用 between ... and ... 去根据 rownum 字段 析取数据(除非你是用的 between 1 and N )SQL> select rownum, empno, ename, job, mgr, hiredate, sal, comm, deptno
  2    from (select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp order by sal DESC) t
  3  where rownum between 1 and 5;    ROWNUM      EMPNO ENAME                JOB                       MGR HIREDATE              SAL    COMM        DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         1       7839 KING                 PRESIDENT                     17-11月-81           5000                10
         2       7902 FORD                 ANALYST                  7566 03-12月-81           3000                20
         3       7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                20
         4       7566 JONES                MANAGER                  7839 02-4月 -81           2975                20
         5       7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                30SQL> select rownum, empno, ename, job, mgr, hiredate, sal, comm, deptno
  2    from (select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp order by sal DESC) t
  3  where rownum between 2and 5;未选定行