之前在工作中碰到一个问题,需要找树结构下的所有子节点,如果用程序写会反复查询数据库,对性能有影响,在网上找了下,可以用Oracle的递归查询,例子如下:createtable test1 (
cid int,
cpid int
)
insertinto test1 (cid,cpid) values(1,0);
insertinto test1 (cid,cpid) values(2,1);
insertinto test1 (cid,cpid) values(3,1);
insertinto test1 (cid,cpid) values(4,2);
insertinto test1 (cid,cpid) values(5,3);
insertint...
from extern_dm_swjg
connect by prior swjg_dm = sj_swjg_dm
start with sj_swjg_dm is null
2. 关于PRIOR
运算符PRIOR被放置于等号前后的位置,决定着查询时的检索顺序。
例2从节点开始自底向上查询[sql] view plaincopy
select swjg_dm,swjg_mc,sj_swjg_dm,swjg_level
from extern_dm_swjg
connect by swjg_dm = prior sj_swjg_dm
start with swjg_dm = ‘16107100004‘
3.使用LEVEL
在具有树结构的表...
递归查询所有树结构:
select * from tableconnect by prior id= parent_idstart with id=总公司的id
以上SQL语句查询出来的树结构有三条分支!
递归查询出的树结构,有多条分支的情况下,确定其中的某一条分支:
SELECT *from(select * from tableconnect by prior id= parent_idstart with id=总公司的id) tconnect by prior id=parent_idstart with id=代理A1的id
以上SQL语句会得到:代理A1>代理A2>代理A3
小白的理解,还请各...
广东省202913684830
--------越秀区202913684831
-------------麓景麓202913684833
-------------越秀路202913684837
--------天河区202913684832
-------------天河路202913684838
第一种方式
select a.treegroup_id,a.treegroup_parent,a.treegroup_name,
(select count(*)
from j_treedepot b
where b.treegroup_parent=a.treegroup_id) sub_cnt
from j_treedepot a
where a.treegroup_parent=202913684830
order by 1;
第...
table test1 (
cid int,
cpid int
)
insert into test1 (cid,cpid) values(1,0);
insert into test1 (cid,cpid) values(2,1);
insert into test1 (cid,cpid) values(3,1);
insert into test1 (cid,cpid) values(4,2);
insert into test1 (cid,cpid) values(5,3);
insert into test1 (cid,cpid) values(6,3);select * from test1
--找根的所有子
select * from test1
start with cpid = 0
Connect by prior cid = cpid--找 子所在的...
Oracle数据库教程,Oracle树结构查询,PRIOR被置于CONNECT BY子句中等号的前面时,则强制从根节点到叶节点的顺序检索,即由父节点 Oracle 树结构的查询和使用
Oracle中 start with...connect by prior 子句用法connect by 是结构化查询中用到的,其基本语法是:select ... from tablename start with 条件1connect by 条件2where 条件3;条件1 是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。条件2...