要求:统计一级部门下及其属于一级部门下的人数及薪资
注:有些部门是直接挂在一级部门下的,有些是挂在二级部门,或者三级部门下,
而它的顶级是一级部门。
表: deparment(部门表)
字段:depid(部门ID),location(中文名称),adminid(上级部门),depgrade(部门级别)表:oCD_depGrade(部门级别表)
字段:code(代码),location(中文名称)
LV0 集团管理
LV1 管理中心
LV2 一级部门
LV3 二级部门
LV4 三级部门
LV5 四级部门
LV6 科
LVT 技术中心本人没分了,谢谢各位大侠~~~!
注:有些部门是直接挂在一级部门下的,有些是挂在二级部门,或者三级部门下,
而它的顶级是一级部门。
表: deparment(部门表)
字段:depid(部门ID),location(中文名称),adminid(上级部门),depgrade(部门级别)表:oCD_depGrade(部门级别表)
字段:code(代码),location(中文名称)
LV0 集团管理
LV1 管理中心
LV2 一级部门
LV3 二级部门
LV4 三级部门
LV5 四级部门
LV6 科
LVT 技术中心本人没分了,谢谢各位大侠~~~!
--1.Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses
--Hierarchical Queries
--START WITH and CONNECT BY PRIOR clauses.SELECT employee_id, manager_id, first_name, last_name
FROM employee_jh
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
1 0 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean Blue
10 1 Kevin Black
11 10 Keith Long
12 10 Frank Howard
13 10 Doreen Penn13 rows selected.--2.Using a Subquery in a START WITH Clause
SELECT LEVEL,
LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee
FROM employee_jh
START WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = 'Kevin' AND last_name = 'Black')
CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE
---------- -------------------------
1 Kevin Black
2 Keith Long
2 Frank Howard
2 Doreen Penn--3.Including Other Conditions in a Hierarchical Query
SELECT LEVEL,
LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
last_name AS employee, salary
FROM employee_jh
WHERE salary <= 50000
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE SALARY
---------- ------------------------- ----------
3 Rob Green 40000
3 Jane Brown 45000
4 Henry Heyson 30000
3 John Grey 30000
4 Jean Blue 29000
3 Keith Long 50000
3 Frank Howard 45000
3 Doreen Penn 470008 rows selected.