【SQL 学习】LEVEL 伪列

最新推荐文章于 2022-02-07 01:37:02 发布

weixin_33774615

最新推荐文章于 2022-02-07 01:37:02 发布

阅读量840

点赞数

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/29721

本文演示了如何在SQL中使用LEVEL伪列从员工表中进行层次查询，展示员工及其上下级关系。通过START WITH和CONNECT BY子句，结合ORDER BY对层级进行排序，展示了不同级别的员工信息，并计算了不同层级的数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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