【SQL 學習】LEVEL 偽列

SQL> select level, employee_id ,manager_id ,first_name ,last_name
  2  from emp
  3  start with employee_id =1
  4  connect by prior employee_id = manager_id
  5  order by level;

     LEVEL EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME              
---------- ----------- ---------- ---------- ----------              
         1           1            James      Smith
         2          10          1 Kevin      Black               
         2           2          1 Ron        Johnson               
         2           4          1 Susan      Jones              
         3          13         10 Doreen     Penn              
         3           7          4 John       Grey               
         3          11         10 Keith      Long
         3           5          2 Rob        Green               
         3           3          2 Fred       Hobbs
         3          12         10 Frank      Howard
         3           6          4 Jane       Brown
         4           8          7 Jean       Blue
         4           9          6 Henry      Heyson
已選擇13行。

已用時間:  00: 00: 00.04
SQL> select count(distinct level)
  2  from emp
  3  start with employee_id =1
  4  connect by prior employee_id =manager_id;

COUNT(DISTINCTLEVEL)
--------------------
                   4                                                                                                             
已用時間:  00: 00: 00.00
SQL> set pagesize 999
SQL> col employee for a25
SQL> select level,
  2  lpad('-',2*level-1) || first_name ||' '|| last_name as employee
  3  from emp
  4  start with employee_id =1
  5  connect by prior employee_id =manager_id;

     LEVEL EMPLOYEE                                                                                                              
---------- -------------------------
         1 -James Smith
         2   -Ron Johnson
         3     -Fred Hobbs
         3     -Rob Green
         2   -Susan Jones
         3     -Jane Brown
         4       -Henry Heyson
         3     -John Grey
         4       -Jean Blue
         2   -Kevin Black
         3     -Keith Long
         3     -Frank Howard
         3     -Doreen Penn
已選擇13行。