PLSQL分级取数据
分级取数据
select employee_id,last_name,job_id,manager_id from employees
start with employee_id=101 --employee_id=101为父
connect by prior employee_id=manager_id;--现在是由父指向子（从上到底）
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ----------
101 Kochhar AD_VP 100
108 Greenberg FI_MGR 101
109 Faviet FI_ACCOUNT 108
110 Chen FI_ACCOUNT 108
111 Sciarra FI_ACCOUNT 108
112 Urman FI_ACCOUNT 108
113 Popp FI_ACCOUNT 108
200 Whalen AD_ASST 101
203 Mavris HR_REP 101
204 Baer PR_REP 101
205 Higgins AC_MGR 101
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ----------
206 Gietz AC_ACCOUNT 205
select employee_id,last_name,job_id,manager_id from employees
start with employee_id=101 --employee_id=101为父
connect by prior manager_id=employee_id;--现在是由子指向父（从底向上）
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ----------
101 Kochhar AD_VP 100
100 King AD_PRES
select employee_id,last_name,job_id,manager_id from employees
start with employee_id=101
connect by prior employee_id=manager_id
and employee_id!=108;
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ----------
101 Kochhar AD_VP 100
200 Whalen AD_ASST 101
203 Mavris HR_REP 101
204 Baer PR_REP 101
205 Higgins AC_MGR 101
206 Gietz AC_ACCOUNT 205
select employee_id,last_name,job_id,manager_id from employees
where employee_id!=108
start with employee_id=101
connect by prior employee_id=manager_id;
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ----------
101 Kochhar AD_VP 100
109 Faviet FI_ACCOUNT 108
110 Chen FI_ACCOUNT 108
111 Sciarra FI_ACCOUNT 108
112 Urman FI_ACCOUNT 108
113 Popp FI_ACCOUNT 108
200 Whalen AD_ASST 101
203 Mavris HR_REP 101
204 Baer PR_REP 101
205 Higgins AC_MGR 101
206 Gietz AC_ACCOUNT 205
用LEVEL和LPAD格式化分级报告
select lpad(last_name,length(last_name)+(LEVEL*2)-2,'$') as org_chart,level
from employees
start with employee_id=100
connect by prior employee_id=manager_id;
最新文章
- mongodb安装配置
- KendoUI系列：ComboBox
- 【matlab】随意记录
- ZBrush中的纹理-水手该怎样进行绘制
- DB2中的ROW_NUMBER() OVER()函数用法
- 在这个年纪，打DOTA冲分有那么可笑么？
- SQLSERVER连接池内部机制
- HDOJ(HDU) 2164 Rock, Paper, or Scissors?
- FUDCon - FedoraProject
- lambda 3
- [物理学与PDEs]第1章习题3 常场强下电势的定解问题
- pyothon学习笔记2-元组
- 小程序getLocation出现的各种问题汇总
- GitHubDesktop权限问题解决办法
- (转载)javascript将base64编码的图片数据转换为file并提交
- 团队项目-NABCD
- Python学习---装饰器的学习1210
- 20155338 2016-2017-2《Java程序设计》第1周学习总结
- Linux中常用的关机和重新启动命令
- mui 页面跳转