【SQL 学习】LEVEL 伪列

最新推荐文章于 2026-02-28 23:40:38 发布

转载最新推荐文章于 2026-02-28 23:40:38 发布 · 371 阅读

本文通过一个具体例子展示了如何使用SQL的层次查询功能来获取组织结构数据。利用START WITH和CONNECT BY PRIOR子句从根节点开始递归地检索员工及其上级管理者的关系，并按层级排序显示结果。

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行。