遇到一个问题:多个表左连接,排序,取第几页数据问题:
SELECT *
FROM (SELECT *
FROM (SELECT ROWNUM AS ROWNO, CARD.CARDID
FROM CARD LEFT OUTER JOIN
EMPLOYEE ON
CARD.EMPLOYEEID = EMPLOYEE.EMPLOYEEID LEFT OUTER JOIN
ACCESSGROUP ON
CARD.ACCESSGROUPID = ACCESSGROUP.ACCESSGROUPID ORDER BY cardID)
WHERE ROWNUM <= 15) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO > 0
这样取出第0-15是没有问题,但rownum并不是第1计数的,它是不是:先将数据库中所有记录按cardID排序,再找ROWNUM <= 15 ,但后面的条件是TABLE_ALIAS.ROWNO >0
是不起作用的:(如果把TABLE_ALIAS.ROWNO > 0改成TABLE_ALIAS.ROWNO > 5,并不是取出第5-10行,而是仍然这15行),结果:
ROWNO CARDID
---------- ----------
662 4119
663 4120
664 4121
665 4122
666 4123
667 4124
668 4125
669 4126
670 4127
671 4138
672 4139
673 4140
674 4141
675 4142若改成
SELECT *
FROM (SELECT *
FROM (SELECT ROWNUM AS ROWNO, CARD.CARDID
FROM CARD LEFT OUTER JOIN
EMPLOYEE ON
CARD.EMPLOYEEID = EMPLOYEE.EMPLOYEEID LEFT OUTER JOIN
ACCESSGROUP ON
CARD.ACCESSGROUPID = ACCESSGROUP.ACCESSGROUPID)
WHERE ROWNUM <= 15 ORDER BY cardID) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO > 0
它是不是: 先将数据库中找ROWNUM <= 15(最上面的15条记录),然后把这15条记录按cardID排序,此时全局数据库并没有按cardID排序,但rownum是从1计,:(如果把TABLE_ALIAS.ROWNO > 0改成TABLE_ALIAS.ROWNO > 5,可以取出第5-10行,但没有排序):
ROWNO CARDID
---------- ----------
1 4937
2 4938
3 4939
4 4940
5 4941
6 4942
7 4943
8 4944
9 4945
10 4946
11 4947
12 4948
13 4949
14 4950如果现在想在全局数据库排序后再取第5-10行记录,如何?
参考别人的做法:第一种方法在没有多表关联,只有一张表条件是可以做到的,但一旦关联,不知道为什么rownum不从1计??
SELECT *
FROM (SELECT *
FROM (SELECT ROWNUM AS ROWNO, CARD.CARDID
FROM CARD LEFT OUTER JOIN
EMPLOYEE ON
CARD.EMPLOYEEID = EMPLOYEE.EMPLOYEEID LEFT OUTER JOIN
ACCESSGROUP ON
CARD.ACCESSGROUPID = ACCESSGROUP.ACCESSGROUPID ORDER BY cardID)
WHERE ROWNUM <= 15) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO > 0
这样取出第0-15是没有问题,但rownum并不是第1计数的,它是不是:先将数据库中所有记录按cardID排序,再找ROWNUM <= 15 ,但后面的条件是TABLE_ALIAS.ROWNO >0
是不起作用的:(如果把TABLE_ALIAS.ROWNO > 0改成TABLE_ALIAS.ROWNO > 5,并不是取出第5-10行,而是仍然这15行),结果:
ROWNO CARDID
---------- ----------
662 4119
663 4120
664 4121
665 4122
666 4123
667 4124
668 4125
669 4126
670 4127
671 4138
672 4139
673 4140
674 4141
675 4142若改成
SELECT *
FROM (SELECT *
FROM (SELECT ROWNUM AS ROWNO, CARD.CARDID
FROM CARD LEFT OUTER JOIN
EMPLOYEE ON
CARD.EMPLOYEEID = EMPLOYEE.EMPLOYEEID LEFT OUTER JOIN
ACCESSGROUP ON
CARD.ACCESSGROUPID = ACCESSGROUP.ACCESSGROUPID)
WHERE ROWNUM <= 15 ORDER BY cardID) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO > 0
它是不是: 先将数据库中找ROWNUM <= 15(最上面的15条记录),然后把这15条记录按cardID排序,此时全局数据库并没有按cardID排序,但rownum是从1计,:(如果把TABLE_ALIAS.ROWNO > 0改成TABLE_ALIAS.ROWNO > 5,可以取出第5-10行,但没有排序):
ROWNO CARDID
---------- ----------
1 4937
2 4938
3 4939
4 4940
5 4941
6 4942
7 4943
8 4944
9 4945
10 4946
11 4947
12 4948
13 4949
14 4950如果现在想在全局数据库排序后再取第5-10行记录,如何?
参考别人的做法:第一种方法在没有多表关联,只有一张表条件是可以做到的,但一旦关联,不知道为什么rownum不从1计??
非主键排序,先取rownum<=15,再排序
主键排序,先排序,再取rownum<=15
SELECT *
FROM (SELECT *
FROM (SELECT ROWNUM AS ROWNO, CARD.CARDID
FROM CARD LEFT OUTER JOIN
EMPLOYEE ON
CARD.EMPLOYEEID = EMPLOYEE.EMPLOYEEID LEFT OUTER JOIN
ACCESSGROUP ON
CARD.ACCESSGROUPID = ACCESSGROUP.ACCESSGROUPID ORDER BY cardID)
WHERE ROWNUM <= 15) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO > 0
这样取出第0-15是没有问题,但rownum并不是第1计数的,它是不是:先将数据库中所有记录按cardID排序,再找ROWNUM <= 15 ,但后面的条件是TABLE_ALIAS.ROWNO >0
是不起作用的:(如果把TABLE_ALIAS.ROWNO > 0改成TABLE_ALIAS.ROWNO > 5,并不是取出第5-15行,而是仍然这15行,结果1:
ROWNO CARDID
---------- ----------
662 4119
663 4120
664 4121
665 4122
666 4123
667 4124
668 4125
669 4126
670 4127
671 4138
672 4139
673 4140
674 4141
675 4142 若改成
SELECT *
FROM (SELECT *
FROM (SELECT ROWNUM AS ROWNO, CARD.CARDID
FROM CARD LEFT OUTER JOIN
EMPLOYEE ON
CARD.EMPLOYEEID = EMPLOYEE.EMPLOYEEID LEFT OUTER JOIN
ACCESSGROUP ON
CARD.ACCESSGROUPID = ACCESSGROUP.ACCESSGROUPID)
WHERE ROWNUM <= 15 ORDER BY cardID) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO > 0
它是不是: 先将数据库中找ROWNUM <= 15(最上面的15条记录),然后把这15条记录按cardID排序,此时全局数据库并没有按cardID排序,但rownum是从1计,:(如果把TABLE_ALIAS.ROWNO > 0改成TABLE_ALIAS.ROWNO > 5,可以取出第5-15行,但没有排序),结果2:
ROWNO CARDID
---------- ----------
1 4937
2 4938
3 4939
4 4940
5 4941
6 4942
7 4943
8 4944
9 4945
10 4946
11 4947
12 4948
13 4949
14 4950 如果现在想在全局数据库排序后再取第5-10行记录,如何?
参考别人的做法:第一种方法在没有多表关联,只有一张表条件是可以做到的,但一旦关联,不知道为什么rownum不从1计??
结果3:
SELECT *
FROM (SELECT *
FROM (SELECT ROWNUM AS ROWNO, CARD.CARDID
FROM CARD
ORDER BY cardID)
WHERE ROWNUM <= 15) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO > 5
ROWNO CARDID
------ ----------
6 4124
7 4125
8 4126
9 4127
10 4138
11 4139
12 4140
13 4141
14 4142
15 4143如果现在想要结果3,且有关联,如何做到?
这和SQL语句的执行顺序有关。"WHERE ROWNUM <= 15" 先执行,"ORDER BY cardID"后执行。所以ROWNUM <= 15的记录会先被过滤掉。用第一种方法就可以解决你的问题了。有什么问题吗?来点数据比较好。
第二种方法:有关联,全局数据库没有排序,rownum从1计,后面的条件起作用
第三种方法:无关联,全局数据库排序,rownum从1计,后面的条件起作用
现在想要一种:有关联,全局数据库排序,rownum从1计,后面的条件起作用
改成
TABLE_ALIAS.ROWNO >= 1 试试看。既然只需要前15行数据,那么过滤条件"TABLE_ALIAS.ROWNO > 0"就可以去掉了。
Oracle分页存储过程CREATE OR REPLACE PACKAGE PACKAGE_PAGE AS
TYPE CURSOR_PAGE IS REF CURSOR;
PROCEDURE PROC_PAGE(
P_CURPAGE NUMBER, --当前页
P_PAGESIZE NUMBER, --每页大小
P_TABLENAME VARCHAR2, --表名EMP E
P_WHERE VARCHAR2, --查询条件E.ENAME LIKE '%S%'
P_TABLECOLUMN VARCHAR2, --查询列E.ID,E.ENAME,E.JOB
P_ORDER VARCHAR2, --排序E.ENAME DESC
P_ROWCOUNT OUT NUMBER, --总条数,输出参数
P_PAGECOUNT OUT NUMBER, --总页数
P_CURSOR OUT CURSOR_PAGE); --结果集
END PACKAGE_PAGE;CREATE OR REPLACE PACKAGE BODY PACKAGE_PAGE
IS
--存储过程
PROCEDURE PROC_PAGE(
P_CURPAGE NUMBER,
P_PAGESIZE NUMBER,
P_TABLENAME VARCHAR2,
P_WHERE VARCHAR2,
P_TABLECOLUMN VARCHAR2,
P_ORDER VARCHAR2,
P_ROWCOUNT OUT NUMBER,
P_PAGECOUNT OUT NUMBER,
P_CURSOR OUT CURSOR_PAGE
)
IS
V_COUNT_SQL VARCHAR2(2000);
V_SELECT_SQL VARCHAR2(2000);
BEGIN
--查询总条数
V_COUNT_SQL:='SELECT COUNT(*) FROM '||P_TABLENAME;
--连接查询条件(''也属于IS NULL)
IF P_WHERE IS NOT NULL THEN
V_COUNT_SQL:=V_COUNT_SQL||' WHERE '||P_WHERE;
END IF;
--执行查询,查询总条数
EXECUTE IMMEDIATE V_COUNT_SQL INTO P_ROWCOUNT; --DBMS_OUTPUT.PUT_LINE('查询总条数SQL=>'||V_COUNT_SQL);
--DBMS_OUTPUT.PUT_LINE('查询总条数COUNT='||P_ROWCOUNT); --得到总页数
IF MOD(P_ROWCOUNT,P_PAGESIZE)=0 THEN
P_PAGECOUNT:=P_ROWCOUNT/P_PAGESIZE;
ELSE
P_PAGECOUNT:=TRUNC(P_ROWCOUNT/P_PAGESIZE) + 1;
END IF; --如果查询记录大于0则查询结果集
IF P_ROWCOUNT>0 AND P_CURPAGE>=1 AND P_CURPAGE <=P_PAGECOUNT THEN --查询所有(只有一页)
IF P_ROWCOUNT <=P_PAGESIZE THEN
V_SELECT_SQL:='SELECT '||P_TABLECOLUMN||' FROM '||P_TABLENAME;
IF P_WHERE IS NOT NULL THEN
V_SELECT_SQL:=V_SELECT_SQL||' WHERE '||P_WHERE;
END IF;
IF P_ORDER IS NOT NULL THEN
V_SELECT_SQL:=V_SELECT_SQL||' ORDER BY '||P_ORDER;
END IF;
ELSIF P_CURPAGE=1 THEN --查询第一页
V_SELECT_SQL:='SELECT '||P_TABLECOLUMN||' FROM '||P_TABLENAME;
IF P_WHERE IS NOT NULL THEN
V_SELECT_SQL:=V_SELECT_SQL||' WHERE '||P_WHERE||' AND ROWNUM <='||P_PAGESIZE;
ELSE
V_SELECT_SQL:=V_SELECT_SQL||' WHERE ROWNUM <='||P_PAGESIZE;
END IF;
IF P_ORDER IS NOT NULL THEN
V_SELECT_SQL:=V_SELECT_SQL||' ORDER BY '||P_ORDER;
END IF;
ELSE --查询指定页
V_SELECT_SQL:='SELECT * FROM (SELECT '|| P_TABLECOLUMN ||',ROWNUM ROW_NUM FROM '|| P_TABLENAME;
IF P_WHERE IS NOT NULL THEN
V_SELECT_SQL:=V_SELECT_SQL||' WHERE '||P_WHERE;
END IF;
IF P_ORDER IS NOT NULL THEN
V_SELECT_SQL:=V_SELECT_SQL||' ORDER BY '||P_ORDER;
END IF;
V_SELECT_SQL:=V_SELECT_SQL||') WHERE ROW_NUM>'||((P_CURPAGE-1)*P_PAGESIZE)||' AND ROW_NUM <='||(P_CURPAGE*P_PAGESIZE);
END IF;
--执行查询
DBMS_OUTPUT.PUT_LINE('查询语句=>'||V_SELECT_SQL);
OPEN P_CURSOR FOR V_SELECT_SQL;
END IF; END PROC_PAGE;
END PACKAGE_PAGE;
TABLE_ALIAS.ROWNO > 0用来作为下限,觉得奇怪的就是结果1和结果3的对比:
结果1只是多加了关联表,为什么rownum就不从1计.而结果3却可以
则得到我想要的结果了.
SQL> SELECT * FROM EMP;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 1014 rows selectedSQL> SELECT * FROM DEPT;DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> SELECT *
2 FROM (SELECT TT.*, ROWNUM RN
3 FROM (SELECT E.*
4 FROM EMP E, DEPT D
5 WHERE E.DEPTNO = D.DEPTNO
6 ORDER BY E.SAL) TT
7 WHERE ROWNUM <= 8)
8 WHERE RN > 0;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 1
7900 JAMES CLERK 7698 1981-12-3 950.00 30 2
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 3
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 4
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 5
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 6
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 88 rows selectedSQL> SELECT *
2 FROM (SELECT TT.*, ROWNUM RN
3 FROM (SELECT E.*
4 FROM EMP E, DEPT D
5 WHERE E.DEPTNO = D.DEPTNO
6 ORDER BY E.SAL) TT
7 WHERE ROWNUM <= 8)
8 WHERE RN > 6;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 8
1.A作主表,关联B,结果3正常.rownum从1计.
2.B作主表,关联A,结果3不正常,rownum并不是从1计