Oracle递归查询笔记

一、创建表结构和插入数据

二、查询所有子节点

三、查询所有父节点

四、查询指定节点的根节点

五、查询指定节点的递归路径

六、递归子类

七、递归父类

一、创建表结构和插入数据

CREATE TABLE "REGION" 
   (	
	"ID" VARCHAR2(36) DEFAULT SYS_GUID() NOT NULL ENABLE, 
	"PARENT_ID" VARCHAR2(36), 
	"NAME" VARCHAR2(255) NOT NULL ENABLE, 
	"LATITUDE" NUMBER(10,6), 
	"LONGITUDE" NUMBER(10,6), 
	PRIMARY KEY ("ID")
   );
  
COMMENT ON COLUMN REGION.ID IS '主键';
COMMENT ON COLUMN REGION.PARENT_ID IS '父键';
COMMENT ON COLUMN REGION.NAME IS '地区名';
COMMENT ON COLUMN REGION.LATITUDE IS '经度';
COMMENT ON COLUMN REGION.LONGITUDE IS '纬度';

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), NULL, '江苏省', 31.2304, 120.663);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '江苏省'), '苏州市', 31.2988, 120.5853);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '苏州市'), '张家港市', 31.8754, 120.5553);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '苏州市'), '吴中区', 31.2622, 120.6446);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '苏州市'), '相城区', 31.3697, 120.646);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '苏州市'), '吴江区', 31.1791, 120.6411);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '张家港市'), '凤凰镇', 31.8754, 120.5553);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '张家港市'), '塘桥镇', 31.8754, 120.5553);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '吴中区'), '木渎镇', 31.2622, 120.6446);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '相城区'), '黄埭镇', 31.3697, 120.646);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '吴江区'), '平望镇', 31.1791, 120.6411);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '吴江区'), '黎里镇', 31.1791, 120.6411);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '江苏省'), '无锡市', 31.5704, 120.3055);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '无锡市'), '锡山区', 31.5887, 120.3573);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '无锡市'), '惠山区', 31.6514, 120.3036);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '无锡市'), '滨湖区', 31.5502, 120.2598);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '无锡市'), '江阴市', 31.9086, 120.2855);

INSERT INTO "REGION" ("ID", "PARENT_ID", "NAME", "LATITUDE", "LONGITUDE") 
VALUES (SYS_GUID(), (SELECT "ID" FROM "REGION" WHERE "NAME" = '无锡市'), '宜兴市', 31.3623, 119.8233);

二、查询所有子节点

SELECT *
FROM REGION 
START WITH NAME = '无锡市'
CONNECT BY PRIOR ID = PARENT_ID

查询结果：

三、查询所有父节点

SELECT *
FROM REGION
START WITH NAME = '塘桥镇'
CONNECT BY PRIOR PARENT_ID = ID

查询结果：

四、查询指定节点的根节点

SELECT r.*,
connect_by_root(r.ID),
connect_by_root(NAME)
FROM REGION r
WHERE r.NAME = '江阴市'
START WITH r.PARENT_ID = '18F2184511D13555E0630100007F8BFA'
CONNECT BY PRIOR r.ID = r.PARENT_ID

注：18F2184511D13555E0630100007F8BFA是江苏省的主键

查询结果：

五、查询指定节点的递归路径

SELECT
	ID,
	PARENT_ID ,
	NAME,
	sys_connect_by_path(NAME, '->') namepath,
	LEVEL
FROM
	REGION
START WITH
	NAME = '苏州市'
CONNECT BY
	PRIOR ID = PARENT_ID

查询结果：

六、递归子类

WITH t ( ID ,PARENT_ID,NAME ) 
AS(
SELECT ID ,PARENT_ID,NAME FROM REGION WHERE NAME='苏州市'
UNION ALL
SELECT d.ID ,d.PARENT_ID , d.NAME FROM t , REGION d
WHERE t.ID = d.PARENT_ID
)
SELECT * FROM t

查询结果：

七、递归父类

WITH t ( ID ,PARENT_ID, NAME)
AS(
SELECT ID ,PARENT_ID, NAME FROM REGION WHERE NAME='塘桥镇'
UNION ALL
SELECT d.ID ,d.PARENT_ID,d.NAME FROM t , REGION d
WHERE t.PARENT_ID = d.ID
)
SELECT * FROM t;

查询结果：