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

关键字: 树型数据结构sql语句的写法
Java代码


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

建立的表结构如下：

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)   
);  

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 字段为空则表明这条记录为根结点。

简单的分级查询：

找出根结点，执行如下语句：Java代码

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

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

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

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

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

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);  

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);

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

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

   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不能包函子查询。

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

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

SELECT lname, emp_id, manager_emp_id
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id 
level伪列--返回每行属于哪一层，使用如下语句：Java代码 
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;  

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;

如何检查员工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  

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 

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

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;  

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;