• Oracle 层次化查询


     create table employees(
        employee_id integer constraint employees_pk primary key,
        manager_id integer constraint employees_fk_employees references employees(employee_id),
        first_name varchar2(10) not null,
        last_name varchar2(10) not null,
        title varchar2(20),
        salary number(6,0)
    );
    
    insert into employees(employee_id,first_name,last_name,title,salary) values(1,'James','Smith','CEO',800000);
    insert into employees values(2,1,'Ron','Johnson','Sales Manager',600000);
    insert into employees values(3,2,'Fred','Hobbs','Sales Person',200000);
    insert into employees values(4,1,'Susan','Jones','Support Manager',500000);
    insert into employees values(5,2,'Rob','Green','Sales Person',40000);
    insert into employees values(6,4,'Jane','Brown','Support Person',45000);
    insert into employees values(7,4,'Jone','Grey','Support Person',30000);
    insert into employees values(8,7,'Jean','Blue','Support Person',29000);
    insert into employees values(9,6,'Henry','Heyson','Support Person',30000);
    insert into employees values(10,1,'Kenvin','Black','Ops Manager',100000);
    insert into employees values(11,10,'Keith','Long','Ops Person',50000);
    insert into employees values(12,10,'Frank','Howard','Ops Person',50000);
    insert into employees values(13,10,'Doreen','Penn','Ops Person',47000);
    
    

     

    员工的关系式一个树形结构,要提取不同员工之间的关系可以使用select 语句的connect by 和start with 子句执行层次化查询。

    语法:
      select [level],column,...
      from table
      [where where_condition]
      start with start_condition connect by prior prior_condition
     
      level 是一个伪列,代表树的第几层。根节点的值是1,根节点的子节点是2,以此类推。
      start_condition 定义了层次化查询的起点。当编写层次化查询时必须指定start with 子句。例如:将start_condition 定义为 employee_id=1,表示从员工Id=1开始查询。
      prior_condition 定义了父行和子行之间的关系。当编写层次化查询时必须指定prior_codition子句。例如将prior_condition 定义为 employee_id=manager_id,表示父节点的employee_id和子节点的manager_id之间存在相等关系。

    select employee_id,manager_id,first_name,last_name 
    from employees 
    start with employee_id=1 connect by prior employee_id=manager_id;

     查询的结果是:

    1		James	Smith
    2	1	Ron	Johnson
    3	2	Fred	Hobbs
    5	2	Rob	Green
    4	1	Susan	Jones
    6	4	Jane	Brown
    9	6	Henry	Heyson
    7	4	Jone	Grey
    8	7	Jean	Blue
    10	1	Kenvin	Black
    11	10	Keith	Long
    12	10	Frank	Howard
    13	10	Doreen	Penn

     使用LEVEL 展示员工所处的层次:

    select level,employee_id,manager_id,first_name,last_name from employees start with employee_id=1 connect by prior employee_id=manager_id order by level;

    查询结果:

    1	1		James	Smith
    2	10	1	Kenvin	Black
    2	2	1	Ron	Johnson
    2	4	1	Susan	Jones
    3	13	10	Doreen	Penn
    3	7	4	Jone	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

     格式化层次查询结果:

    select level,lpad('-',2*(level-1))||first_name||' '||last_name as employee from employees start with employee_id=1 connect by prior employee_id=manager_id;

     结果:

    1	James Smith
    2	 -Ron Johnson
    3	   -Fred Hobbs
    3	   -Rob Green
    2	 -Susan Jones
    3	   -Jane Brown
    4	     -Henry Heyson
    3	   -Jone Grey
    4	     -Jean Blue
    2	 -Kenvin Black
    3	   -Keith Long
    3	   -Frank Howard
    3	   -Doreen Penn
  • 相关阅读:
    POJ 1265 Pcik定理
    POJ 1380 坐标旋转
    POJ 1788
    POJ 3714 平面最近点对
    POJ 1905 二分
    POJ 1151 矩形面积并
    POJ 1654 多边形面积
    ZOJ 1010 判断简单多边形+求面积
    about work
    Python 打印 不换行
  • 原文地址:https://www.cnblogs.com/wei-zw/p/8797785.html
Copyright © 2020-2023  润新知