关联查询 rowNum 问题

Question

遇到一个问题:多个表左连接,排序,取第几页数据问题:
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计??

Answer 1

rownum始终是从一开始的关于在于where rownum<=15 和order by的顺序
非主键排序，先取rownum<=15，再排序
主键排序，先排序，再取rownum<=15

Answer 2

遇到一个问题:多个表左连接,排序,取第几页数据问题: 
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,且有关联,如何做到?

Answer 3

WHERE ROWNUM <= 15 ORDER BY cardID ...
这和SQL语句的执行顺序有关。"WHERE ROWNUM <= 15" 先执行，"ORDER BY cardID"后执行。所以ROWNUM <= 15的记录会先被过滤掉。用第一种方法就可以解决你的问题了。有什么问题吗？来点数据比较好。

Answer 4

第一种方法:有关联,全局数据库排序,但rownum没从1计,所以后面的条件不起作用
第二种方法:有关联,全局数据库没有排序,rownum从1计,后面的条件起作用
第三种方法:无关联,全局数据库排序,rownum从1计,后面的条件起作用
现在想要一种:有关联,全局数据库排序,rownum从1计,后面的条件起作用

Answer 5

把 TABLE_ALIAS.ROWNO > 0
改成
TABLE_ALIAS.ROWNO >= 1 试试看。既然只需要前15行数据，那么过滤条件"TABLE_ALIAS.ROWNO > 0"就可以去掉了。

Answer 6

--下面是用存储过程实现的分页代码，你可以参考一下：
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;

Answer 7

谢谢关注,呵呵,代码原因,所以目前不考虑用存储过程.
TABLE_ALIAS.ROWNO > 0用来作为下限,觉得奇怪的就是结果1和结果3的对比:
结果1只是多加了关联表,为什么rownum就不从1计.而结果3却可以

Answer 8

如果修改结果1中的: TABLE_ALIAS.ROWNO > 666   
则得到我想要的结果了.    

Answer 9

--我的怎么就没有问题呢？你写的分页语句是没错的。你看一下相关列是不是有空值所以影响了排序？比如CARDID
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

Answer 10

试了下,并不是因为数据null造成的,还没找出原因.奇怪

Answer 11

那就应该是数据的问题了，你可以根据那个SQL-3从里面一步一步来调，边调边看数据。

Answer 12

做了这样的实现:表A和表B关联,A在数据库中是按主键顺序存储的,B没有.
1.A作主表,关联B,结果3正常.rownum从1计.
2.B作主表,关联A,结果3不正常,rownum并不是从1计