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;
LEVEL LNAME EMP_ID MANAGER_EMP_ID
---------- -------------------- ---------- --------------
1 KING 7839
2 JONES 7566 7839
3 SCOTT 7788 7566
4 ADAMS 7876 7788
3 FORD 7902 7566
4 SMITH 7369 7902
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 WARD 7521 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 JAMES 7900 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
14 rows selected.
|
下面来看一些复杂的分等级查询操作。
返回层数：
SELECT MAX(LEVEL)
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id;
MAX(LEVEL)
----------
4
|
如何检查员工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;
EMP_ID LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE
---------- ---------- ---------- -------------- ---------- ---------
7369 SMITH 20 7902 800 17-DEC-80
|
列出员工及他管理的员工的工资总数：
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;
LNAME SALARY SUM_SALARY
-------------------- ---------- ----------
SMITH 800 800
ALLEN 1600 1600
WARD 1250 1250
JONES 2000 9900
MARTIN 1250 1250
BLAKE 2850 9400
CLARK 2450 3750
SCOTT 3000 4100
KING 5000 28050
TURNER 1500 1500
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3800
MILLER 1300 1300
14 rows selected.
|
分等级查询里的排序：
SELECT LEVEL, LPAD(' ',2*(LEVEL - 1)) || lname "EMPLOYEE",
emp_id, manager_emp_id
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id
ORDER SIBLINGS BY lname;
LEVEL EMPLOYEE EMP_ID MANAGER_EMP_ID
---------- -------------------- ---------- --------------
1 KING 7839
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 JAMES 7900 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 WARD 7521 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
2 JONES 7566 7839
3 FORD 7902 7566
4 SMITH 7369 7902
3 SCOTT 7788 7566
4 ADAMS 7876 7788
|
到某节点的路径：
SELECT SYS_CONNECT_BY_PATH(lname, '#')
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id;
SYS_CONNECT_BY_PATH(LNAME,'#')
---------------------------------
#KING
#KING#JONES
#KING#JONES#SCOTT
#KING#JONES#SCOTT#ADAMS
#KING#JONES#FORD
#KING#JONES#FORD#SMITH
#KING#BLAKE
#KING#BLAKE#ALLEN
#KING#BLAKE#WARD
#KING#BLAKE#MARTIN
#KING#BLAKE#TURNER
#KING#BLAKE#JAMES
#KING#CLARK
#KING#CLARK#MILLER
|
posted on 2007-10-12 09:49
tianjuchuan 阅读(222)
评论(0) 编辑 收藏 引用 所属分类:
sql