存储数据关系的时,表结构关系会存在一种树形关系,比如通过一个parentId字段来关联,下面用一个简单的小例子来描述Oracle中的递归查询创建表CREATE TABLE COMMENTS (COMMENTID INTEGER, PARENTID INTEGER, ARTICLEID INTEGER, COMMENTBODY VARCHAR2(500));
插入初始化数据INSERT INTO COMMENTS (COMMENTID, PARENTID, ARTICLEID, COMMENTBODY) VALUES (1, null, 1, ‘第一条评论‘);
INSERT INTO COMMENTS (COMMENTID, PARENTID, ART...
一、创建数据1.1、建立表与插入数据CREATE TABLE DISTRICT
(ID NUMBER(10) NOT NULL,PARENT_ID NUMBER(10),NAME VARCHAR2(255 BYTE) NOT NULL
);ALTER TABLE DISTRICT ADD (CONSTRAINT DISTRICT_PKPRIMARY KEY(ID));ALTER TABLE DISTRICT ADD (CONSTRAINT DISTRICT_R01 FOREIGN KEY (PARENT_ID) REFERENCES DISTRICT (ID));insert into DISTRICT (id, parent_id, name)
values (1, null,...
通过子节点向根节点追朔.select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid通过根节点遍历子节点(不包含根节点).select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid通过根节点遍历子节点(包含根节点).select * from persons.dept start with deptid=0 connect by prior deptid=paredeptid可通过level 关键字查询所在层次.select a.*,level from persons....
SQL：select*from PT_ORG_INFO START WITH id=‘102‘ CONNECT BY PRIOR id=par_idView Code连表递归查询SQL：select info.*from PT_CAMERA_INFO info
join (select*from PT_ORG_INFO START WITH id=‘102‘ CONNECT BY PRIOR id=par_id) org on org.id=info.org_id
where1=1View Code 原文：http://www.cnblogs.com/s0611163/p/7238801.html
之前在工作中碰到一个问题，需要找树结构下的所有子节点，如果用程序写会反复查询数据库，对性能有影响，在网上找了下，可以用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...
http://msdn.microsoft.com/zh-cn/library/ms186243(v=SQL.90).aspx。
MsSql的语法如下：WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
递归执行的语义如下：将 CTE 表达式拆分为定位点成员和递归成员。
运行定位点...
一、递归查询
1、表机构SQL> desc comm_org_subjectionName Null? Type----------------------------------------- -------- ----------------------------ORG_SUBJECTION_ID NOT NULL VARCHAR2(32) 子键ORG_ID NOT NULL VARCHAR2(32)FATHER_ORG_ID NOT NULL VARCHAR2(32) 父键LOCKE...
SELECT SUBSTR(SYS_CONNECT_BY_PATH(tb.name,‘->‘),3) name
2 FROM table tb
3 START WITH nvl(tb.parentid,0)=0
4 CONNECT BY PRIOR ID=mt.parentid
5 ;在Oracle中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。sys_connect_by_path(字段名, 2个字段之间的连接符号)，注意这里的连接符号不要使用逗号，oracle会报错，
如...
with view_test as (2 3 select ‘A‘ AS ID,‘X‘ AS P_ID,‘C‘ AS C_ID,‘1‘ AS IND_EX FROM DUAL4 5 UNION6 select ‘C‘ AS ID,‘A‘ AS P_ID,‘B‘ AS C_ID,‘1_1‘ AS IND_EX FROM DUAL7 UNION8 select ‘B‘ AS ID,‘C‘ AS P_ID,‘A‘ AS C_ID,‘1_2‘ AS IND_EX FROM DUAL9 /*UNION
10 select ‘A‘ AS ID,‘B‘ AS P_ID,‘C‘ AS C_ID FROM DUAL*/
11 UNION
12 sel...
SELECT ID,
2 mt.materialtypename,
3 mt.parenttypeid
4 FROM material_type mt;
使用递归还是与LEVEL1 SELECT ID,
2 mt.materialtypename,
3 mt.parenttypeid,
4 SUBSTR(SYS_CONNECT_BY_PATH(mt.materialtypename,‘->‘),3) DisplayLevel,
5 level
6 FROM material_type mt
7 START WITH nvl(mt.parenttypeid,0)=0
8 CONNECT BY PRIOR ID=...
CREATE TABLE SC_DISTRICT
(ID NUMBER(10) NOT NULL,PARENT_ID NUMBER(10),NAME VARCHAR2(255 BYTE) NOT NULL
);ALTER TABLE SC_DISTRICT ADD (CONSTRAINT SC_DISTRICT_PKPRIMARY KEY(ID));ALTER TABLE SC_DISTRICT ADD (CONSTRAINT SC_DISTRICT_R01 FOREIGN KEY (PARENT_ID) REFERENCES SC_DISTRICT (ID));
插入数据:
INSERT INTO SC_DISTRICT(ID,NAME) VALUES(1,‘四川省‘);INSERT I...
select a.accountcodeid, a.accountcode, a.accountcodename,2 SYS_CONNECT_BY_PATH(a.accountcodename, ‘\‘) as fullname, a.parentcodeid, level3 from fm_accountcode a4 where a.accountcodeid >= 100005 start with a.parentcodeid is null6 connect by prior a.accountcodeid = a.parentcodeid;7 8 select a.accountcodeid, a.accountcode, a.accountcodename,9 SYS_CONNECT_BY_PATH(a.accountcodename,...
…start with…connect by…prior例如：有一个单位（unit）表 有三个字段 ID ,NAME,PARENTID(单位ID，单位名称，上级的单位ID)
1.1 查询该单位下面的所有单位select t.id,t.name,t.parentid from unit t start with t.id=‘单位的ID‘ connect by prior t.id= t.parentid
1.2 查询该单位上面的所有单位select t.id,t.name,t.parentid from unit t start with t.id=‘单位的ID‘ connect by t.id= prior ...
1<code plain"="" style="white-space: pre-wrap; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.8em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !im...
where <Conditional-1> //过滤 start with <Conditional-2> //递归起点 connect by <Conditional-3>; //连接限定
注意：
1、prior放在子节点端，则表示扫描树是以start with指定的节点作为根节点从上往下扫描
2、prior放在父节点端，则表示扫描树是以start with指定的节点作为最低层子节点，从下往上扫描。
3、去掉prior关键字表示不进行递归
例子截图：
Oracle递归树标签：本文系统来源：http://my.oschina.net...
1 2
1 3
1 4
4 5
5 6
1 7
7 8
8 9
9 10
4。总结
上面的例子，作用是实现遍历所有节点。如果prior 放在 connect by后面的话，那就是从上查找