rownum与level在某些场景的用法是一样的,但在某些场景就产生天渊之别的结果,提醒大家平时慎用level。
以下是两者一些场景中使用的比较:
----rownum 与level的用法:
--例子:
题目:打印2016-06-15~2016-07-14之间的连续日期。
--使用rownum查询:
SQL> select (to_date('2016-06-15','yyyy-mm-dd') + rownum-1) service_date
2 from dual
3 connect by rownum <= (to_date('2016-07-14','yyyy-mm-dd') -
4 to_date('2016-06-15','yyyy-mm-dd')+1);
SERVICE_DATE
-------------------
2016-06-15 00:00:00
2016-06-16 00:00:00
2016-06-17 00:00:00
2016-06-18 00:00:00
... ...
2016-07-12 00:00:00
2016-07-13 00:00:00
2016-07-14 00:00:00
30 rows selected.
--level查询:
SQL> select (to_date('2016-06-15','yyyy-mm-dd') + level-1) service_date
2 from dual
3 connect by level <= (to_date('2016-07-14','yyyy-mm-dd') -
4 to_date('2016-06-15','yyyy-mm-dd')+1);
SERVICE_DATE
-------------------
2016-06-15 00:00:00
2016-06-16 00:00:00
2016-06-17 00:00:00
2016-06-18 00:00:00
2016-06-19 00:00:00
2016-06-20 00:00:00
2016-06-21 00:00:00
... ...
2016-07-12 00:00:00
2016-07-13 00:00:00
2016-07-14 00:00:00
30 rows selected.
以上两种结果是一样的。
--分解查看:
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-10-21 18:44:06
SQL> select (sysdate + rownum) from dual;
(SYSDATE+ROWNUM)
-------------------
2016-10-22 18:46:11
SQL> select (sysdate + rownum-1) from dual
(SYSDATE+ROWNUM-1)
-------------------
2016-10-21 18:46:40
--使用level查看:
SQL> select (sysdate + level-1) from dual connect by level<=5;
(SYSDATE+LEVEL-1)
-------------------
2016-10-21 18:50:46
2016-10-22 18:50:46
2016-10-23 18:50:46
2016-10-24 18:50:46
2016-10-25 18:50:46
SQL> select (sysdate +level) from dual connect by level<=5;
(SYSDATE+LEVEL)
-------------------
2016-10-22 18:51:10
2016-10-23 18:51:10
2016-10-24 18:51:10
2016-10-25 18:51:10
2016-10-26 18:51:10
SQL> select (sysdate + rownum) from dual connect by rownum <=5;
(SYSDATE+ROWNUM)
-------------------
2016-10-22 18:52:02
2016-10-23 18:52:02
2016-10-24 18:52:02
2016-10-25 18:52:02
2016-10-26 18:52:02
--使用两个分别查询emp表(表中有14条数据):
--使用level查询emp表:
SQL> col rownum for 99;
SQL> select rownum,empno,ename,job from emp
2 where rownum <=10;
ROWNUM EMPNO ENAME JOB
------ ---------- ---------- ---------
1 7369 SMITH CLERK
2 7499 ALLEN SALESMAN
3 7521 WARD SALESMAN
4 7566 JONES MANAGER
... ...
9 7839 KING PRESIDENT
10 7844 TURNER SALESMAN
10 rows selected.
SQL> select empno,ename,job from emp
2 connect by level<=2;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
... ...
EMPNO ENAME JOB
---------- ---------- ---------
7934 MILLER CLERK
210 rows selected.
可以看到两者的产生的结果是不一样的。
--使用两者往测试空表中插入数据:
--使用 rownum :
SQL> create table t7(id number(2),created date);
Table created.
--使用rownum进行插入:
SQL> insert into t7 select * from t6 where rownum <=5;
5 rows created.
--查看表t7的数据:
SQL> select * from t7;
ID CREATED
---------- ---------
1 21-OCT-16
2 21-OCT-16
3 21-OCT-16
4 21-OCT-16
5 21-OCT-16
--使用level:
SQL> insert into t7 select * from t6
2 connect by level <=2;
156 rows created.
--查看表t7的数据:
SQL> select * from t7 order by 1;
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
... ...
SQL> insert into t7 select * from t6
2 connect by level <=2;
156 rows created.
--查看表t7的数据:
SQL> select * from t7 order by 1;
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
... ...
ID CREATED
---------- -------------------
12 2016-10-21 19:02:56
12 2016-10-21 19:02:56
156 rows selected.
--创建表t8测试表:(t7中有4条记录)
SQL> insert into t8 select * from t7
2 connect by level <=3;
84 rows created.
--查看表t8的数据:
SQL> select * from t8 order by 1;
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
2 2016-10-21 19:02:56
ID CREATED
---------- -------------------
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
ID CREATED
---------- -------------------
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
ID CREATED
---------- -------------------
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
ID CREATED
---------- -------------------
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
ID CREATED
---------- -------------------
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
ID CREATED
---------- -------------------
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
84 rows selected.
从以上的测试过程中发现level其实就是层级、乘方的意思。就拿往空表t8中插入数据来说,表t7中有4条记录,
我们利用 表t7的数据查到表t8中,我们执行SQL> insert into t8 select * from t7 connect by level <=3;
最后发现出入了84行:
以下是模拟插入的过程:
----------第一:0+4*1=4;
----------第二:4+4*4=20;
----------第三:4+4*4+4*4*4=84。
总和就是84行,就是这样理解level就可以了。
也即:connect by level<=N, (level一定要与connect by一起用,但rownum可以独立用)
假设原表有X条数据,这样查询使用就是:connect by level<=N就是:
X+X*X+X*X*X*X+X*X*X*X*X+X...N个X相乘的结果,数据量大的话,查询就花销很多内存。
所以没必要是,宁愿使用rownum都不要使用level用来给测试表插入数据。
以下是两者一些场景中使用的比较:
----rownum 与level的用法:
--例子:
题目:打印2016-06-15~2016-07-14之间的连续日期。
--使用rownum查询:
SQL> select (to_date('2016-06-15','yyyy-mm-dd') + rownum-1) service_date
2 from dual
3 connect by rownum <= (to_date('2016-07-14','yyyy-mm-dd') -
4 to_date('2016-06-15','yyyy-mm-dd')+1);
SERVICE_DATE
-------------------
2016-06-15 00:00:00
2016-06-16 00:00:00
2016-06-17 00:00:00
2016-06-18 00:00:00
... ...
2016-07-12 00:00:00
2016-07-13 00:00:00
2016-07-14 00:00:00
30 rows selected.
--level查询:
SQL> select (to_date('2016-06-15','yyyy-mm-dd') + level-1) service_date
2 from dual
3 connect by level <= (to_date('2016-07-14','yyyy-mm-dd') -
4 to_date('2016-06-15','yyyy-mm-dd')+1);
SERVICE_DATE
-------------------
2016-06-15 00:00:00
2016-06-16 00:00:00
2016-06-17 00:00:00
2016-06-18 00:00:00
2016-06-19 00:00:00
2016-06-20 00:00:00
2016-06-21 00:00:00
... ...
2016-07-12 00:00:00
2016-07-13 00:00:00
2016-07-14 00:00:00
30 rows selected.
以上两种结果是一样的。
--分解查看:
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-10-21 18:44:06
SQL> select (sysdate + rownum) from dual;
(SYSDATE+ROWNUM)
-------------------
2016-10-22 18:46:11
SQL> select (sysdate + rownum-1) from dual
(SYSDATE+ROWNUM-1)
-------------------
2016-10-21 18:46:40
--使用level查看:
SQL> select (sysdate + level-1) from dual connect by level<=5;
(SYSDATE+LEVEL-1)
-------------------
2016-10-21 18:50:46
2016-10-22 18:50:46
2016-10-23 18:50:46
2016-10-24 18:50:46
2016-10-25 18:50:46
SQL> select (sysdate +level) from dual connect by level<=5;
(SYSDATE+LEVEL)
-------------------
2016-10-22 18:51:10
2016-10-23 18:51:10
2016-10-24 18:51:10
2016-10-25 18:51:10
2016-10-26 18:51:10
SQL> select (sysdate + rownum) from dual connect by rownum <=5;
(SYSDATE+ROWNUM)
-------------------
2016-10-22 18:52:02
2016-10-23 18:52:02
2016-10-24 18:52:02
2016-10-25 18:52:02
2016-10-26 18:52:02
--使用两个分别查询emp表(表中有14条数据):
--使用level查询emp表:
SQL> col rownum for 99;
SQL> select rownum,empno,ename,job from emp
2 where rownum <=10;
ROWNUM EMPNO ENAME JOB
------ ---------- ---------- ---------
1 7369 SMITH CLERK
2 7499 ALLEN SALESMAN
3 7521 WARD SALESMAN
4 7566 JONES MANAGER
... ...
9 7839 KING PRESIDENT
10 7844 TURNER SALESMAN
10 rows selected.
SQL> select empno,ename,job from emp
2 connect by level<=2;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
... ...
EMPNO ENAME JOB
---------- ---------- ---------
7934 MILLER CLERK
210 rows selected.
可以看到两者的产生的结果是不一样的。
--使用两者往测试空表中插入数据:
--使用 rownum :
SQL> create table t7(id number(2),created date);
Table created.
--使用rownum进行插入:
SQL> insert into t7 select * from t6 where rownum <=5;
5 rows created.
--查看表t7的数据:
SQL> select * from t7;
ID CREATED
---------- ---------
1 21-OCT-16
2 21-OCT-16
3 21-OCT-16
4 21-OCT-16
5 21-OCT-16
--使用level:
SQL> insert into t7 select * from t6
2 connect by level <=2;
156 rows created.
--查看表t7的数据:
SQL> select * from t7 order by 1;
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
... ...
SQL> insert into t7 select * from t6
2 connect by level <=2;
156 rows created.
--查看表t7的数据:
SQL> select * from t7 order by 1;
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
... ...
ID CREATED
---------- -------------------
12 2016-10-21 19:02:56
12 2016-10-21 19:02:56
156 rows selected.
--创建表t8测试表:(t7中有4条记录)
SQL> insert into t8 select * from t7
2 connect by level <=3;
84 rows created.
--查看表t8的数据:
SQL> select * from t8 order by 1;
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
2 2016-10-21 19:02:56
ID CREATED
---------- -------------------
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
ID CREATED
---------- -------------------
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
ID CREATED
---------- -------------------
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
ID CREATED
---------- -------------------
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
ID CREATED
---------- -------------------
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
ID CREATED
---------- -------------------
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
84 rows selected.
从以上的测试过程中发现level其实就是层级、乘方的意思。就拿往空表t8中插入数据来说,表t7中有4条记录,
我们利用 表t7的数据查到表t8中,我们执行SQL> insert into t8 select * from t7 connect by level <=3;
最后发现出入了84行:
以下是模拟插入的过程:
----------第一:0+4*1=4;
----------第二:4+4*4=20;
----------第三:4+4*4+4*4*4=84。
总和就是84行,就是这样理解level就可以了。
也即:connect by level<=N, (level一定要与connect by一起用,但rownum可以独立用)
假设原表有X条数据,这样查询使用就是:connect by level<=N就是:
X+X*X+X*X*X*X+X*X*X*X*X+X...N个X相乘的结果,数据量大的话,查询就花销很多内存。
所以没必要是,宁愿使用rownum都不要使用level用来给测试表插入数据。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2126956/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2126956/