树型数据结构SQL语句的写法

 在一个关系数据库中，可以表现分等级的信息。

建立的表结构如下：

CREATE TABLE employee (
emp_id          NUMBER (4) CONSTRAINT emp_pk PRIMARY KEY,
fname           VARCHAR2 (15) NOT NULL, 
lname           VARCHAR2 (15) NOT NULL, 
dept_id         NUMBER (2) NOT NULL,
manager_emp_id  NUMBER (4) CONSTRAINT emp_fk REFERENCES employee(emp_id),
salary          NUMBER (7,2) NOT NULL,
hire_date       DATE NOT NULL, 
job_id          NUMBER (3)
);

 

如果manager_emp_id 字段为空则表明这条记录为根结点。

简单的分级查询：

1. 找出根结点，执行如下语句：

   SELECT emp_id, lname, dept_id, manager_emp_id, salary, hire_date 
   FROM employee 
   WHERE manager_emp_id IS NULL;
   

    
2. 找出结点的父结点，本例中就是显示每个雇员的经理姓名，执行如下语句：

   SELECT e.lname "Employee", m.lname "Manager" 
   FROM employee e, employee m 
   WHERE e.manager_emp_id = m.emp_id;
   

    
3. 找出所有的页节点，本例中就是找出所有的普通雇员，使用如下语句：

   SELECT emp_id, lname, dept_id, manager_emp_id, salary, hire_date 
   FROM employee e
   WHERE emp_id NOT IN 
   (SELECT manager_emp_id FROM employee 
   WHERE manager_emp_id IS NOT NULL);

4. 能完成同样功能的语句如下：

5.    SELECT emp_id, lname, dept_id, manager_emp_id, salary, hire_date 
               FROM employee e
               WHERE NOT EXISTS 
               (SELECT emp_id FROM employee e1 WHERE e.emp_id = e1.manager_emp_id);
   

    

         

复杂的查询：oralce提供对于Ansi Sql的一些扩展。

start with........connect by 子句

proir 操作符

level伪列

语法如下：

start with connection1 connect by connection2

start with connection1 指定根元素。能满足connection1认为是根元素。它可以包函子查询。

connect by connection2指定父行与子行的关系，connection2必须包函prior操作符，用来确定父行里的列，connection2不能包函子查询。

1. 查找父结点与其子结点的结果，使用如下语句：

   SELECT lname, emp_id, manager_emp_id
   FROM employee
   START WITH manager_emp_id IS NULL
   CONNECT BY PRIOR emp_id = manager_emp_id

    
2. level伪列--返回每行属于哪一层，使用如下语句：

   SELECT level, lname, emp_id, manager_emp_id
               FROM employee
               START WITH manager_emp_id IS NULL
               CONNECT BY manager_emp_id = PRIOR emp_id;
   

    
3. 如何检查员工a是否可以管理员工b，由于a可能不是b的主管经理，所以我们不能直接通过manager_emp_id来判断，可以使用如下语句：

   SELECT emp_id, lname, dept_id, manager_emp_id, salary, hire_date 
               FROM employee
               WHERE lname = 'SMITH'
               START WITH lname = 'JONES'
               CONNECT BY manager_emp_id = PRIOR emp_id

    
4. 列出员工及他管理的员工的工资总数：

   SELECT t2.lname, t2.salary,
               (SELECT SUM(t1.salary) FROM employee t1
               START WITH t1.lname = t2.lname
               CONNECT BY t1.manager_emp_id = PRIOR t1.emp_id) sum_salary
               FROM employee t2;