文章详情

  • 游戏榜单
  • 软件榜单
关闭导航
热搜榜
热门下载
热门标签
php爱好者> php文档>分层查询(遍历树)

分层查询(遍历树)

时间:2010-08-12  来源:lzdyxin


1、建表
SQL> create table employees (employee_id number,last_name varchar2(20),JOB_ID varchar2(20),MANAGER_ID number);
表已创建。
2、表结构
SQL> desc employees;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------
 EMPLOYEE_ID                                        NUMBER
 LAST_NAME                                          VARCHAR2(20)
 JOB_ID                                             VARCHAR2(20)
 MANAGER_ID                                         NUMBER

3、插入一些数据
insert into employees values (100,'King','AD_PRES',null);
insert into employees values (101,'Kochhar','AD_VP',100);
insert into employees values (102,'De Haan','AD_VP',100);
insert into employees values (103,'Hunold','IT_PROG',102);
insert into employees values (104,'Emst','IT_PROG',103);
insert into employees values (107,'Lorentz','IT_PROG',103);
insert into employees values (124,'Mourgos','ST_MAN',100);
insert into employees values (141,'Rajs','ST_CLERK',124);
insert into employees values (142,'Davies','ST_CLERK',124);
insert into employees values (143,'Matos','ST_CLERK',124);
insert into employees values (144,'Vargas','ST_CLERK',124);
insert into employees values (149,'Zlotkey','SA_MAN',100);
insert into employees values (174,'Abel','SA_REP',149);
insert into employees values (176,'Taylor','SA_REP',149);
insert into employees values (178,'Gant','SA_REP',149);
insert into employees values (200,'Whalen','AD_ASST',101);
insert into employees values (201,'Hartstein','MK_MAN',100);
insert into employees values (202,'Fay','MK_REP',201);
insert into employees values (205,'Higgins','AC_MGR',101);
insert into employees values (206,'Gietz','AC_ACCOUNT',205);
4、查询的例子
从某点从下向上
SQL> SELECT employee_id, last_name, job_id, manager_id
  2  FROM   employees
  3  START  WITH  employee_id = 101
  4  CONNECT BY PRIOR manager_id = employee_id;
EMPLOYEE_ID LAST_NAME            JOB_ID               MANAGER_ID
----------- -------------------- -------------------- ----------
        101 Kochhar              AD_VP                       100
        100 King                 AD_PRES
自上而下
SQL> SELECT  last_name||' reports to '||
  2  PRIOR   last_name "Walk Top Down"
  3  FROM    employees
  4  START   WITH last_name = 'King'
  5  CONNECT BY PRIOR employee_id = manager_id ;
Walk Top Down
----------------------------------------------------
King reports to
Kochhar reports to King
Whalen reports to Kochhar
Higgins reports to Kochhar
Gietz reports to Higgins
De Haan reports to King
Hunold reports to De Haan
Emst reports to Hunold
Lorentz reports to Hunold
Mourgos reports to King
Rajs reports to Mourgos
Walk Top Down
----------------------------------------------------
Davies reports to Mourgos
Matos reports to Mourgos
Vargas reports to Mourgos
Zlotkey reports to King
Abel reports to Zlotkey
Taylor reports to Zlotkey
Gant reports to Zlotkey
Hartstein reports to King
Fay reports to Hartstein
已选择20行。 按层读出
SQL> COLUMN org_chart FORMAT A12
SQL> SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')
  2  AS org_chart
  3  FROM employees
  4  START WITH last_name='King'
  5  CONNECT BY PRIOR employee_id=manager_id;
ORG_CHART
------------
King
__Kochhar
____Whalen
____Higgins
______Gietz
__De Haan
____Hunold
______Emst
______Lorentz

ORG_CHART
------------
__Mourgos
____Rajs
____Davies
____Matos
____Vargas
__Zlotkey
____Abel
____Taylor
____Gant
__Hartstein
____Fay
已选择20行。
相关阅读 更多 +
排行榜 更多 +
辰域智控app

辰域智控app

系统工具 下载
网医联盟app

网医联盟app

运动健身 下载
汇丰汇选App

汇丰汇选App

金融理财 下载