Oralce 层次查询

1.     初始化数据库，创建表和导入数据：
   

   create table bicycle(
   part_id number(5) constraint pk_bicycle_part_id primary key,
   parent_id number(5) constraint fk_bicycle_pid references bicycle(part_id),
   part_name varchar2(30) not null,
   mp_cost number(9, 2),
   describe varchar2(30)
   );
   
   insert into bicycle values(1, null, '自行车', 379.28, '装配');
   insert into bicycle values(2, 1, '导向系统', 101.11, '制造');
   insert into bicycle values(3, 1, '驱动系统', 159.56, '制造');
   insert into bicycle values(4, 1, '其他部分', 118.61, '制造和采购');
   insert into bicycle values(5, 2, '车把', 37.28, '制造');
   insert into bicycle values(6, 2, '前叉', 24.35, '制造');
   insert into bicycle values(7, 2, '前轴', 19.67, '制造');
   insert into bicycle values(8, 2, '前轮', 19.81, '制造');
   insert into bicycle values(9, 7, '前轴棍', 8.16, '制造');
   insert into bicycle values(10, 7, '前轴身', 4.82, '制造');
   insert into bicycle values(11, 7, '前轴碗', 6.69, '制造');
   insert into bicycle values(12, 10, '前轴管', 1.61, '制造');
   insert into bicycle values(13, 10, '前花盘', 3.21, '制造');
   insert into bicycle values(14, 3, '脚蹬', 18.99, '制造');
   insert into bicycle values(15, 3, '中轴', 25.27, '制造');
   insert into bicycle values(16, 3, '链条', 21.65, '制造');
   insert into bicycle values(17, 3, '飞轮', 29.12, '制造');
   insert into bicycle values(18, 3, '后轴', 31.72, '制造');
   insert into bicycle values(19, 3, '后轮', 32.81, '制造');
   insert into bicycle values(20, 17, '外套', 9.35, '制造');
   insert into bicycle values(21, 17, '平档', 5.82, '制造');
   insert into bicycle values(22, 17, '芯子', 5.11, '制造');
   insert into bicycle values(23, 17, '千斤', 6.56, '制造');
   insert into bicycle values(24, 17, '钢珠', 2.28, '采购');
   insert into bicycle values(25, 4, '车架', 81.78, '制造');
   insert into bicycle values(26, 4, '车闸', 15.26, '制造');
   insert into bicycle values(27, 4, '链罩', 7.10, '采购');
   insert into bicycle values(28, 4, '车铃', 4.33, '采购');
   insert into bicycle values(29, 4, '车锁', 5.02, '采购');
   insert into bicycle values(30, 4, '支架', 5.12, '制造');

    
2. 层次查询：

   select level, column_name, expression, ...
   from table_name
   [where where_condition]
   start with start_condition
   connect by prior prior_condition;
   --level 关键字是一个伪列，用于显示树的层次
   
   eg:
   -- 从上而下， 并过滤掉一个驱动系统分支和前轴管节点
   select level,part_id,part_name,mp_cost
   from bicycle
   where part_name <> '前轴管'
   start with part_id = 1
   connect by prior part_id = parent_id and part_name <>'驱动系统'
   order by level;
   --从下而上
   select level,part_id,part_name,mp_cost
   from bicycle
   start with part_id = 13
   connect by prior  parent_id = part_id
   order by level;