语法

select * from 表名 where 条件1 start with 条件2 connect by prior 当前表字段=级联字段

start with 与 connect by prior 语句完成递归记录，形成一颗树形结构，通常可以在具有层次结构的表中使用。

start with：表示开始的记录

connect by prior：指定与当前记录关联时的字段关系

效果演示

1.建立一个城市代码关系表，id为主键，parent_id为上级id，具有层级结构

drop table "city";
create table city(
id number(5) primary key,
name varchar(50) not null,
parent_id number(5)
);

2.插入演示数据

insert into city values(1,'province_1',null);
insert into city values(2,'city_1',1);
insert into city values(3,'city_2',1);
insert into city values(4,'city_3',1);
insert into city values(5,'tower_1_1',2);
insert into city values(6,'tower_1_2',2);
insert into city values(7,'tower_1_3',2);
insert into city values(8,'tower_1_4',2);
insert into city values(9,'tower_2_1',3);
insert into city values(10,'tower_2_2',3);
insert into city values(11,'tower_3_1',4);

3.查询语句及演示结果

查询当前选中省份/城市及所有所属市县

查询条件一：顶级节点，拥有多层下级节点

select * from city start with id=1 connect by prior id=parent_id; 

结果：递归返回自己和所有下级节点

查询条件二：只有子节点

select * from city start with id=2 connect by prior id=parent_id;

结果：返回自己和子节点

查询条件三：没有下级节点

select * from city start with id=5 connect by prior id=parent_id;

结果：返回自己

查询当前城市及其上级省市

查询条件一：无父节点（prior 后面紧接着的字段，一定是当前记录字段所表示的值，如下例子：parent_id的值是id=1这条记录的parent_id）

select * from city start with id=1 connect by id= prior parent_id; 
--或者
select * from city start with id=1 connect by prior parent_id=id

结果：返回只有自己

查询条件二：有父节点

select * from city start with id=2 connect by prior parent_id=id; 

结果：返回自己和父节点

查询条件三：拥有多层上级节点

select * from city start with id=5 connect by prior parent_id=id; 

结果：递归返回自己和所有上级节点

加上where条件筛选，先查询出树形结构再进行条件筛选

查询条件：去除自己

select * from city where id!=5 start with id=5 connect by prior parent_id=id; 

结果：

后续：

connect by prior 后面的条件要保证不要造成死循环-即表内循环

--如病人表：
create table "patient"(
    "id" NUMBER(32) primary key,
    "name" VARCHAR2(32),
    "gender" VARCHAR2(10),
    "partner_id" NUMBER(32)
);

--配对-男女双飞相互包含
insert into "patient" values(1, '甲', '女', 2);
insert into "patient" values(2, '乙', '男', 1);
insert into "patient" values(3, '丙', '女', 4);
insert into "patient" values(4, '丁', '男', 3);
insert into "patient" values(5, '戊', '女', 6);
insert into "patient" values(6, '戌', '男', 5);
--未配对
insert into "patient" values(7, '亥', '女', NULL);
insert into "patient" values(8, '辛', '女', NULL);

--查出病人按配对方式，未配对接后
--已配对的按配对方式一对一对排序，未配对的接在最前或最后
--要根据业务来具体处理，嵌套过多的sql对数据库性能影响很大
--SIBLINGS 子排序
select * from (
	select t.*, level level_1 from "patient"  t start with "partner_id" is not null connect by prior "partner_id"="id" and prior "gender"='男'
	order SIBLINGS by "id" desc
	) where (level_1=1 and "id" not in (
		select "id" from (
			select t.*, level level_1 from "patient"  t start with "partner_id" is not null connect by prior "partner_id"="id" and prior "gender"='男'
			) where level_1 =2)) or level_1=2

参考：https://blog.csdn.net/u013492963/article/details/18551701

参考：https://blog.csdn.net/huofuman960209/article/details/102976927