Oracle递归查询connect by用法

select [level],*　　feom table_name 　
start with 条件1
connect by [ nocycle ]  prior 条件2
where 条件3　ORDER　BY [ sibilings ]　排序字段

select *  from employees
 start with last_name = 'Raphaely'
   connect by prior employee_id = manager_id;  --找下属
-- connect by employee_id = prior manager_id;  --找上司，第一种,修改prior关键字位置
-- connect by prior manager_id = employee_id;  --找上司，第二种,prior关键字不动 调换后面的 employee_id = manager_id 逻辑关系的顺序

select * from employees
 start with manager_id is null
 connect by prior employee_id = manager_id and last_name <> 'Raphaely';

select count(distinct LEVEL)   from EMPLOYEES
 start with MANAGER_ID is null
 connect by prior EMPLOYEE_ID = MANAGER_ID;

SELECT * FROM employees 
 WHERE  last_name = 'Mavris' 
 START WITH last_name = 'Kochhar' --Kochhar的所有雇员
 CONNECT BY PRIOR employee_id = manager_id;

select lpad(' ',level*2,' ')||emp_name as name,emp_id,manager_id,salary,level from employee
  start with manager_id=0
  connect by prior emp_id=manager_id

sys_connect_by_path(<列名>,<连接串>)

select sys_connect_by_path(t.dept_name,'-->'),t.dept_id, t.dept_name, t.dept_code,t.par_dept_id, level  from SYS_DEPT t  
start with t.dept_id = 'e01d6' 
connect by prior t.dept_id = t.par_dept_id
order by level, t.dept_code

select last_name "Employee", connect_by_root last_name "Manager",sys_connect_by_path(last_name, ' -> ') "Path" from hr.employees
where level > 1
connect by prior employee_id = manager_id
order by last_name, length("Path");

order siblings by <expre> asc|desc ;

select t.employee_id,t.manager_id,t.first_name,t.salary, sys_connect_by_path(t.first_name, '->'), level from hr.employees t
 start with manager_id is null
 connect by prior employee_id = manager_id
 order by salary desc;

select t.employee_id,t.manager_id,t.first_name,t.salary,sys_connect_by_path(t.first_name, '->'),level from hr.employees t
 start with manager_id is null
 connect by prior employee_id = manager_id
 order siblings by salary desc;

select rownum from dual connect by rownum<=4

select 10+(rownum-1) from dual connect by rownum<=14-10+1

select chr(ascii('a')+(rownum-1)) from dual connect by rownum<=ascii('d')-ascii('a')+1

select to_date('2011-01-05','yyyy-mm-dd')+(rownum-1) from dual connect by rownum<=to_date('2011-01-10','yyyy-mm-dd')-to_date('2011-01-05','yyyy-mm-dd')+1

select sysdate - (to_number(to_char(sysdate - 1, 'd')) - 1) - (rownum - 1) * 7 as startDate,
       sysdate + (7 - to_number(to_char(sysdate - 1, 'd'))) - (rownum - 1) * 7 as endDate,
       to_number(to_char(sysdate, 'iw')) - rownum + 1 as weekIndex
  from dual
connect by level<= 12;--将level改成rownum可以实现同样的效果

select substr(id,
        instr(id,',',1,rownum)+1,
        instr(id,',',1,rownum+1) - instr(id,',',1,rownum)-1)--根据逗号的位置进行拆分
from (select ','||'a1,b1,d1'||',' as id from dual) --前后各加一个逗号
connect by rownum<=length(id)-length(replace(id,',',''))-1

select REGEXP_SUBSTR('a1,b1,d1', '[^,]+', 1, rownum) as newport 
    from dual connect by rownum <= REGEXP_COUNT('a1,b1,d1', '[^,]+');

with temp as
(select 'a' as A,'b' as B from dual 
  uNIOn
  select 'c' as C,'d' as D from dual 
)
  select * from temp;

select  to_date('2011-01-05','yyyy-mm-dd')+(rownum-1)  as ydate_date,
  to_char(to_date('2011-01-05','yyyy-mm-dd')+(rownum-1),'yyyy') as ydate_month
 from dba.tab_cols where  to_date('2011-01-05','yyyy-mm-dd')+(rownum-1) != to_date('2060-01-05','yyyy-mm-dd')