關於connet by的學習

今天，我學習了oracle 的connect by。搞了半天我才弄明白這個connect by的意思。

connect by的作用就是遞迴查詢，什麼意思呢，下面上例項來理解。

關於connet by的學習

SQL> select level,ename,job ,ename from scott.emp emp start with ename in ('WARD') connect by empno = prior mgr;

LEVEL ENAME JOB ENAME

---------- ------------------------------ --------- ------------------------------

1 WARD SALESMAN WARD

2 BLAKE MANAGER BLAKE

3 KING PRESIDENT KING

解釋：

/*START WITH 表示遞迴的起點，而 CONNECT BY 描述遞迴步驟，決定了爬樹的方向，也就是如何從第 n 步發展到第 (n + 1) 步。由於在歸結 ename 時需要區分第 n 步和第 (n + 1) 步，因此使用 PRIOR （優先的意思）來表明 empno 屬於第 n 步，而 mgr 屬於第 (n + 1) 步。

先從start with ename=’WARD’這一行資料開始，因為prior 為mgr，那麼第一步的mgr就是7698，其對應的empno也必須是7698，這樣第二步就會輸出7698即blake；7698對應的mgr是7839，所以這個mgr=7839將作為第三步的prior，而empno=7839其實就是king。以此類推。我們看到empno=7839即 king對應的mgr為空，那輸出就到此結束。

其中的LEVEL是偽列。這個列的作用是表明產生行的遞迴步驟 n 屬於第幾步。在這個例子中，它表示領導與僱員之間相差的管理等級加 1（因為 LEVEL 一開始為 1）。

參考文件：

如果你讀懂了上面的例子，那麼就自己分析一下下面這個例子吧。

SQL> col ename for a30

SQL> select level,empno,lpad(ename,length(ename)+level*2-2,' ') ename

2 from emp

3 start with ename='KING'

4 connect by prior empno=mgr;

LEVEL EMPNO ENAME

---------- ---------- ------------------------------

1 7839 KING

2 7566 JONES

3 7788 SCOTT

4 7876 ADAMS

3 7902 FORD

4 7369 SMITH

2 7698 BLAKE

3 7499 ALLEN

3 7521 WARD

3 7654 MARTIN

3 7844 TURNER

LEVEL EMPNO ENAME

---------- ---------- ------------------------------

3 7900 JAMES

2 7782 CLARK

3 7934 MILLER

14 rows selected.

資料具有樹狀結構，就可以使用分級查詢，如上面例子的領導下有員工、一級主管、二級主管、三級主管等。

好了，既然上面兩個小例子你都看懂了，那我們在看一下下面這個例子。

需求：輸出某個月的所有日期。

解決辦法：

select to_date('201504','yyyymm')+(rownum-1)

s_date from dual

connect by rownum<=last_day(to_date('201504','yyyymm')) - to_date('201504','yyyymm')+1;

S_DATE

-------------------

2015/04/01 00:00:00

2015/04/02 00:00:00

2015/04/03 00:00:00

2015/04/04 00:00:00

2015/04/05 00:00:00

2015/04/06 00:00:00

2015/04/07 00:00:00

2015/04/08 00:00:00

2015/04/09 00:00:00

2015/04/10 00:00:00

2015/04/11 00:00:00

S_DATE

-------------------

2015/04/12 00:00:00

2015/04/13 00:00:00

2015/04/14 00:00:00

2015/04/15 00:00:00

2015/04/16 00:00:00

2015/04/17 00:00:00

2015/04/18 00:00:00

2015/04/19 00:00:00

2015/04/20 00:00:00

2015/04/21 00:00:00

2015/04/22 00:00:00

S_DATE

-------------------

2015/04/23 00:00:00

2015/04/24 00:00:00

2015/04/25 00:00:00

2015/04/26 00:00:00

2015/04/27 00:00:00

2015/04/28 00:00:00

2015/04/29 00:00:00

2015/04/30 00:00:00

30 rows selected.

看到上面的SQL是不是有點懵？別急，我來一一分解。

下面這條語句可以輸入每個自然月的第一天的日期。

關於connet by的學習

下面這個SQL可以輸出每個自然月的最後一天的日期：

關於connet by的學習

下面兩個SQL就是在輸出一個月有多少天。

關於connet by的學習

connect by是關鍵字，rownum是偽列， connect by rownum就是遞迴輸出數字，因為connect by是會產生遞增序列的。

SQL> select rownum from dual connect by rownum <10;

ROWNUM

----------

1

2

3

4

5

6

7

8

9

9 rows selected.

好了，看了上面的分析，那你就很容易理解下面這條語句的含義了，就不多說了。

select to_date('201504','yyyymm')+(rownum-1)

s_date from dual

connect by rownum<=last_day(to_date('201504','yyyymm')) - to_date('201504','yyyymm')+1;

另外,連續輸入數字還能這麼做：

SQL> SELECT LEVEL

2 FROM dual

3 CONNECT BY LEVEL < 10;

LEVEL

----------

1

2

3

4

5

6

7

8

9

9 rows selected.

簡單的分頁，用level,不能用rownum：

SQL> select level from dual where level between 10 and 20 connect by level<=20;

   LEVEL
----------
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20

11 rows selected

SQL> select rownum+10 from dual connect by rownum<11;

ROWNUM+10

----------

11

12

13

14

15

16

17

18

19

20

10 rows selected.

關於connet by的學習

附件列表

相關文章