oracle SQL简介（二）

函数，树结构查询，连接查询，子查询， 优化

2.   树结构查询(connect by)

从树干开始，向上查：

select level,empno,ename,mgr from emp

start with (empno= 7369)

connect by prior mgr=empno;

 

select level, EMPLOYEE_ID,FIRST_NAME,MANAGER_ID from employees

start with (EMPLOYEE_ID= 206)

connect by prior MANAGER_ID=EMPLOYEE_ID;

 

从树根开始，向下查：

select level,empno,ename,mgr from emp

start with (mgr is null)

connect by prior empno=mgr;

 

select level, EMPLOYEE_ID,FIRST_NAME,MANAGER_ID from employees

start with (MANAGER_ID is null)

connect by prior EMPLOYEE_ID = MANAGER_ID;

 

删除节点，下级保留

select level,empno,ename,mgr from emp

where ename<>'BLAKE'

start with (mgr is null)

connect by prior empno=mgr;

删除树干,包括下属

select level,empno,ename,mgr from emp

start with (mgr is null)

connect by prior empno=mgr

and ename<>'BLAKE';

生成年份列表

select 1949 + rownum - 1 as year  from dual

connect by 1949 + rownum - 1 <= extract(year from sysdate)

order by year desc;