set linesize 100

select empno,ename,job,mgr,deptno,level
from emp
start with mgr is null
connect by prior empno=mgr;

--- display like a real tree
select empno,lpad(' ',level*2 - 1,' ')|| ename name,job,mgr,deptno,level
from emp
start with mgr is null
connect by prior empno=mgr;

-- to set the order of the query
select empno,lpad(' ',level*2 - 1,' ')|| ename name,job,mgr,deptno,level
from emp
start with mgr is null
connect by prior empno=mgr
order siblings by ename
;

siblings后面不能用数字,也不能出现LEVEL PRIOR ROWNUM

---connect by 构造数列
select rownum rown from dual connect by rownum<5;

--也可以换成LEvel
select rownum rown from dual connect by level<5;

--从非1开始
select rownum+15 rown from dual connect by rownum<5; --begin with 15

select rownum-5 rown from dual connect by rownum<5; --begin with -4

select 10- rownum rown from dual connect by rownum<5; --递减序列,9 8 7 6

select 3* rownum-9 rown from dual connect by rownum<5; --等差序列,-6 -3 0 3

--等比数列
select power(2,rownum) rown from dual connect by rownum<5;

--间隔非1的数列
select sin(rownum/100+0.09) rown from dual connect by rownum<(0.8 - 0.1)/0.01;

----------------------------------------------
--排列组合

--求组合
column xmlpath format a15
select rown,sys_connect_by_path(rown,' ') xmlpath
from
(select rownum rown from dual connect by rownum<4)
connect by rown;

sys_connect_by_path是9i开始提供的函数，将从根节点到当前节点的数据以指定的连接字符串连接起来，只能在有connect by 子句的语句中使用。

---connect_by_root 取的是根节点的值
select connect_by_root(rown) rrown,rown,sys_connect_by_path(rown,',') xmlpath
from
(select rownum rown from dual connect by rownum<4)
connect by rown;

--只取2个元素的组合
select connect_by_root(rown) rrown,rown,sys_connect_by_path(rown,',') xmlpath
from
(select rownum rown from dual connect by rownum<4)
where level=2
connect by rown;

--从N个不同的元素中取出R个元素的组合的sql
select connect_by_root(rown) rrown,rown,sys_connect_by_path(rown,',') xmlpath
from
(select rownum rown from dual connect by rownum<(&n+1))
where level=&r
connect by rown;

--上述语句在n很大 r很小的情况下，会产生很多无用的结果，改造一下
with para as (select &n n,&r r from dual)
select connect_by_root(rown) rrown,rown,sys_connect_by_path(rown,',') xmlpath
from
(select rownum rown from dual connect by rownum<(&n+1))
where level=&r
connect by rown--add this statement
and level<=r
;

---------------------------------------------------------------------
---求排列
column xmlpath format a15
select level,sys_connect_by_path(rown,',') xmlpath
from
(select rownum rown from dual connect by level<4)
connect by NOCYCLE
rown<>prior rown
order by level,2
;

---------------------------------------------------------------------
--逆转求出上下级关系路径 connect_by_isleaf
select empno,ename,job,mgr,deptno,level lv,sys_connect_by_path(empno,'/') c1,sys_connect_by_path(ename,'/') c2,connect_by_isleaf isleaf
from emp
start with mgr is null
connect by prior empno=mgr;

--自下而上的倒树
--可惜该语句只能在11g上面运行出正确的结果
select empno,ename,job,mgr,deptno,level lv,c1,c2
,sys_connect_by_path(empno,'/') a1,sys_connect_by_path(ename,'/') a2,connect_by_isleaf isleaf2
from
(
select empno,ename,job,mgr,deptno,level lv,sys_connect_by_path(empno,'/') c1,sys_connect_by_path(ename,'/') c2,connect_by_isleaf isleaf
from emp
start with mgr is null
connect by prior empno=mgr
)
start with isleaf=1
connect by lv=prior lv-1
and empno=prior empno
;

---换成嵌套表可以
create table tree_test as (select empno,ename,job,mgr,deptno,level lv,sys_connect_by_path(empno,'/') c1,sys_connect_by_path(ename,'/')

c2,connect_by_isleaf isleaf
from emp
start with mgr is null
connect by prior empno=mgr)
;

select count(*) from tree_test;

select empno,ename,job,mgr,deptno,level lv,c1,c2
,sys_connect_by_path(empno,'/') a1,sys_connect_by_path(ename,'/') a2,connect_by_isleaf isleaf2
from tree_test
start with isleaf=1 --8条记录,可总记录数为14
connect by lv=prior lv-1
and empno=prior mgr
;

--换一种更为灵活的办法
with t as (select empno,ename,job,mgr,deptno,level lv,sys_connect_by_path(empno,'/') c1,sys_connect_by_path(ename,'/') c2,connect_by_isleaf

isleaf
from emp
start with mgr is null
connect by prior empno=mgr)
select empno,ename,job,mgr,deptno,level lv,c1,c2
,sys_connect_by_path(empno,'/') a1,sys_connect_by_path(ename,'/') a2,connect_by_isleaf isleaf2
from t
start with isleaf=1
connect by lv=prior lv-1
and empno=prior mgr
;

----------------------------------------------
--辅助生成SQL语句生成 and t1.r<=t2.r and t2.r<=t3.r and t3.r<=t4.r and t4.r<=t5.r and t5.r<=t6.r and t6.r<=t7.r

select sys_connect_by_path(str,' and ') statement
from
(select rownum rown,'t'||rownum||'.r<=t'||(rownum+1)||'.r' str from dual connect by rownum<8)
where level=7
connect by rown>prior rown
;

---------------------------------
--拆分字符串
with t as (select 'lastwinner' str from dual)
select substr(str,rownum,1) splitchar from t connect by rownum<=length(str)
;

---------------------------------
--抽签
with t as (select rownum rown from dual connect by rownum<=&n)
select rown from (select rown,rank() over(order by dbms_random.value() desc ) rank from t)
where rank<=&r
;

---------------------------------------
--辅助EXP数据导出通常小表不费太多时间

--挑选大于1000万的表,单独开进程进行导出,可同时开5个进程

select max(sys_connect_by_path(table_name,',')) tablelist from
(select table_name,ceil(sum(num_rows)over(order by table_name asc)/10000000) s,rank() over(order by table_name asc) rk from user_tables
)
connect by rk=prior rk-1
and s=prior s
group by s;

ITPub博客

学习笔记:connect by还能做什么

最新文章