oracle数据库多级树查询及特殊处理函数

select * from employees

按职位级别从高到底查询出每个员工的信息已经他所在的职位级别

select t.employee_id,t.first_name,t.MANAGER_ID,level
from employees t
start with t.MANAGER_ID is null
--start with t.mgr = 7566
--start with t.empno = 7566
connect by  t.employee_id= prior  t.MANAGER_ID

从下往上查询所有的树级别()

start with ……connect by ……prior…………树级别递归查询

select t.employee_id,t.first_name,t.MANAGER_ID,level
from employees t
start with t.MANAGER_ID is null
--start with t.mgr = 7566
--start with t.empno = 7566
connect by prior  t.employee_id=   t.MANAGER_ID

从上级往下级逐级查询

SELECT  RPAD('IVERSON',10,'X') FROM DUAL

结果:IVERSONXXX

SELECT  LPAD('IVERSON',10,'X') FROM DUAL

结果:XXXIVERSON

SELECT RPAD('IVERSON',10) FROM DUAL

结果:IVERSON   ；三个空格

SELECT LPAD('IVERSON',10) FROM DUAL

结果:   IVERSON；三个空格

select rownum+5 from dul connect by rownum<5

select lag(lev,'-') over(order by lev) prevent,lead(lev,'-')over(order by lev) last,lev from(
select rownum+5 as lev from dual connect by rownum<5)

select lag(lev,2) over(order by lev) prevent,lead(lev,2)over(order by lev) last,lev from(
select rownum+5 as lev from dual connect by rownum<5)

select lag(lev,2,-1) over(order by lev) prevent,lead(lev,2,-1)over(order by lev) last,lev from(
select rownum+5 as lev from dual connect by rownum<5)