17,377
社区成员
发帖
与我相关
我的任务
分享请帮忙看看这句SQL, 实现分页查询, 但目前发现排序有问题,请大家帮忙.谢谢
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 字段值应该是最大的, 但实际并不是这样, 而且排序很乱.自己没看出问题, 请大家帮忙 谢谢
...全文
请发表友善的回复…
发表回复
独孤名 2011-08-10
- 打赏
- 举报
学习了........
yeah_90 2011-08-04
- 打赏
- 举报
非常感谢.受益非浅.
luoyoumou1202 2011-08-04
- 打赏
- 举报
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 30
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 2and 5;
未选定行
luoyoumou 2011-08-04
- 打赏
- 举报
-- 请看：
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 是根据排序产生的，其实不然!
luoyoumou 2011-08-04
- 打赏
- 举报
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
luoyoumou 2011-08-04
- 打赏
- 举报
-- 直接 用 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条件的比较，没你这样搞的!