以下是为您整理出来关于【递归查询】合集内容，如果觉得还不错，请帮忙转发推荐。
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, ‘四川省‘)...
drop table tb; create table tb(id varchar(3) , pid varchar(3) , name varchar(10)); insert into tb values(‘001‘ , null , ‘广东省‘); insert into tb values(‘002‘ , ‘001‘ , ‘广州市‘); insert into tb values(‘003‘ , ‘001‘ , ‘深圳市‘) ; insert into tb values(‘004‘ , ‘002‘ , ‘天河区‘) ; insert into tb values(‘005‘ , ‘003‘ , ‘罗湖区‘); insert into tb values(...
一、递归查询 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...
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...
TABLE `treenodes` (`id` int , -- 节点ID`nodename` varchar (60), -- 节点名称`pid` int -- 节点父ID ); 插入测试数据INSERT INTO `treenodes` (`id`, `nodename`, `pid`) VALUES (‘1‘,‘A‘,‘0‘),(‘2‘,‘B‘,‘1‘),(‘3‘,‘C‘,‘1‘), (‘4‘,‘D‘,‘2‘),(‘5‘,‘E‘,‘2‘),(‘6‘,‘F‘,‘3‘), (‘7‘,‘G‘,‘6‘),(‘8‘,‘H‘,‘0‘),(‘9‘,‘I‘,‘8‘), (‘10‘,‘J‘,‘8‘),(‘11‘,‘K‘,‘8‘),(‘12...
TABLE DEPARTMENT (ID INTEGER PRIMARY KEY,NAME VARCHAR(32),PARENT_ID INTEGER REFERENCES DEPARTMENT(ID) );下面我们造几条测试数据INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(1, ‘DEPARTMENT_1‘, NULL); INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(11, ‘DEPARTMENT_11‘, 1); INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(12, ‘DEPARTMENT_12‘, 1); INSERT INTO DEPARTMENT(ID, NAME, PARENT...
SQL Server使用递归查询数据标签：本文系统来源：http://www.cnblogs.com/yourchoose/p/4531221.html
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 表达式拆分为定位点成员和递归成员。 运行定位点...
--cteParents 父亲信息 with cteChildren(EmpID,ParentID) AS ( select EmpID,ParentID from Persons where parentid = 10171 union all select t.EmpID,t.parentid from Persons as t inner join cteChildren as c on t.ParentID = c.Id ), cteParents(EmpID,ParentID) AS ( select EmpID,ParentID from Persons where Id= 10172 union all select t.EmpID,t.parentid from Persons as t inner join cteParents as c ...
--SQLServer 递归查询，主要用于建立树形结构IF OBJECT_ID(Categories) IS NOT NULLDROP TABLE Categories GOCREATE TABLE Categories (CategoryID INT, CategoryName VARCHAR(20),ParentID INT ) GOINSERT INTO Categories(CategoryID, CategoryName, ParentID)SELECT 1, Books, NULL UNION ALLSELECT 2, SQL Server, 1 UNION ALLSELECT 3, ASP.NET, 1 UNION ALLSELECT 4, General, 2 UNION ALLSELECT 5, SSIS, 2 UNION ALLSELECT...