第十章层次查询课堂笔记和练习

    1.查询雇员编号为7876的员工信息，以及从底向上的上级经理信息。

    select * from emp where empno = 7876
    union
    select * from emp where empno = 7788
    union
    select * from emp where empno = 7566
    union
    select * from emp where empno = 7839;
    
    select * from emp 
     start with empno=7876 
       connect by prior mgr = empno;--empno 父列  mgr子列（优先级高）
       --  prior 跟谁谁的级别高
    select * from emp 
     start with empno=7876 
       connect by empno = prior mgr;--同上

    --  查询雇员编号为7839的员工信息，并按照从顶向下的顺序查上级经理信息

    select empno, ename, job, mgr
      from emp
     start with empno = 7839
    connect by prior empno = mgr;
    
    --  通过prior关键字，获取上下级员工信息
    select empno, ename, job, mgr,prior ename, prior mgr
      from emp
     strat with empno = 7839
    connect by prior empno = mgr;

    select level,e.*

      from emp e 
     start with ename='KING' 
       connect by prior empno = mgr; 
    
    --用 LEVEL和LPAD生成分级报告
    select level,
           e.empno,
           e.ename,
           e.mgr,
           lpad(e.ename,length(e.ename) + level*2,'-') 
      from emp e 
     start with ename='KING' 
       connect by prior empno = mgr;

    --去除一个节点：在where中添加判断条件

    1.从上往下查询编号为7839的所有员工信息，不包括编号为7788的员工
        select * 
        from emp 
       where empno<>7788 
       start with empno=7839 
     connect by prior empno = mgr;
    
    --去除一个分支：在connect by后添加判断条件
    1.从上往下查询编号为7839的所有员工信息，不包括编号为7698的员工及分支信息
        select * 
          from emp 
         start with empno=7839 
       connect by prior empno = mgr 
           and empno <> 7698;

    select i.*,rowid from menu i;

    
    --创建菜单表
    create table MENU
    (
      id       NUMBER(5) not null,
      menu     VARCHAR2(50),
      fatherid NUMBER(5)
    )
    --新增菜单数据
    --delete from MENU;
    insert into MENU (id, menu, fatherid) values (0, '总菜单',null);
    insert into MENU (id, menu, fatherid) values (1, '服装', 0);
    insert into MENU (id, menu, fatherid) values (2, '鞋包', 0);
    insert into MENU (id, menu, fatherid) values (3, '美食', 0);
    insert into MENU (id, menu, fatherid) values (4, '运动', 0);
    insert into MENU (id, menu, fatherid) values (5, '女装', 1);
    insert into MENU (id, menu, fatherid) values (6, '男装', 1);
    insert into MENU (id, menu, fatherid) values (7, '户外健身', 4);
    insert into MENU (id, menu, fatherid) values (8, '大码女装', 5);
    commit;
    
    菜单栏
    id  menu   fatherId
    0   总菜单    
    1   服装      0      --一级菜单
    2   鞋包      0     --一级菜单
    3   美食      0     --一级菜单
    4   运动      0     --一级菜单
    
    5   女装      1      --二级菜单
    6   男装      1      --二级菜单
    7   户外健身  4      --二级菜单
    
    8   大码女装  5      --三级菜单
    
    --  1.产生一个报告，显示所有的以编号为0的菜单开始的菜单编号，菜单名称，
    ->  以及父级菜单名称。(中文字符占2个长度)
    select level,
           m.*,
           prior menu,    --获取上级菜单的名称
           length(menu)*2 长度,
           (length(menu)*2 + level * 2) 补全长度,  --！！！中文字符占2个长度
           lpad(menu, length(menu)*2 + level * 2, '*') 格式化菜单信息
      from menu m
     start with id = 0                             --从当前节点开始查询
    connect by prior id = fatherId;
    --prior id 父列(主列):从顶向下  prior fatherId子列(从列) ：从底向上
    
    2.产生一个报告，显示所有的菜单信息，由上往下查询，不包括编号为2与3的分支
    select level,m.* 
      from menu m 
     start with id=0 
       connect by prior id = fatherid 
       and id not in(2,3);