树状结构存储过程中通过递归构建，类似BBS回帖显示，代码共三段：建表，插入数据，创建存储过程显示；1.create table article(id number primary key,cont varchar2(4000),pid number,idleaf number(1), --0为非叶子节点,1为叶子节点alevel number(2)); 2.insert into article values(1,‘蚂蚁大战大象‘,0,0,0);insert into article values(2,‘大象被打趴下了‘,1,0,1);insert into article values(3,‘蚂蚁也不好过‘,1,0,1)...
Oracle中start with...connect by prior子句使用方法connect by 是结构化查询中用到的，其基本的语法是： select ... from tablename start with 条件1 connect by 条件2 where 条件3;例：select * from tablestart with org_id = ‘HBHqfWGWPy‘connect by prior org_id = parent_id; 简单说来是将一个树状结构存储在一张表里，比方一个表中存在两个字段: org_id,parent_id那么通过表示每一条记录的parent是谁，就能够形成一个树状...
存储数据关系的时,表结构关系会存在一种树形关系,比如通过一个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 ...