Oracle 中的递归语句Select...Start With...Connect by prior...的使用 |
您所在的位置：网站首页 › sql语句value意思和用法 › Oracle 中的递归语句Select...Start With...Connect by prior...的使用 |
Oracle 中的递归语句Select...Start With...Connect by prior...的使用
|
Oracle 中的递归语句Select...Start With...Connect by prior...的使用
原创
我是张三丰 2023-04-24 14:55:18 博主文章分类：Oracle数据库 ©著作权 文章标签 数据库 Java Front Office 文章分类 代码人生 ©著作权归作者所有：来自51CTO博客作者我是张三丰的原创作品，请联系作者获取转载授权，否则将追究法律责任在做项目中，我们经常会遇到树形数据，在oracle树查询的最重要的就是select…start with…connect by…prior语法了。根据该语法，我们可以将一个表形结构的以树的顺序列出来。
SQL脚本用来创建表、序列、索引、测试数据等、 drop table t_dept; drop sequence seq_dept; drop index t_index_parent_id; drop index t_index_dept_code;--创建表; create table t_dept ( deptid number not null primary key, dentname_en varchar2(200) not null, deptname_cn varchar2(200) not null, deptcode varchar2(200) not null, parent_id number(10) default -1); --创建序列; create sequence seq_dept start with 100 increment by 1 minvalue 1 maxvalue 1000000 nocycle cache 20; --创建索引; create index t_index_parent_id on scott.t_dept(parent_id); create index t_index_dept_code on t_dept(deptcode); --插入测试数据; insert into scott.t_dept values(seq_dept.nextval,'Front Office Department','前台部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Financial Department','财务部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Logistics Department','后勤部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Recruiting Department','招聘部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Development department','开发部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Dngineering department','工程部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Front Office Department_001','前台接待分部','0'||seq_dept.nextval,100); insert into scott.t_dept values(seq_dept.nextval,'Front Office Department_002','前台分流部','0'||seq_dept.nextval,100); insert into scott.t_dept values(seq_dept.nextval,'Front Office Department_003','前台引导客流部','0'||seq_dept.nextval,100); insert into scott.t_dept values(seq_dept.nextval,'Receptionist Department_001','前台接待普通客户部','0'||seq_dept.nextval,106); insert into scott.t_dept values(seq_dept.nextval,'Receptionist Department_002','前台接待中级客户部','0'||seq_dept.nextval,106); insert into scott.t_dept values(seq_dept.nextval,'Receptionist Department_003','前台接待高级客户部','0'||seq_dept.nextval,106); insert into scott.t_dept values(seq_dept.nextval,'Sweep the toilet Department','扫厕所分部','0'||seq_dept.nextval,102); insert into scott.t_dept values(seq_dept.nextval,'Logistical support Department','后勤保障部','0'||seq_dept.nextval,102); insert into scott.t_dept values(seq_dept.nextval,'Clean the health','打扫卫生分部','0'||seq_dept.nextval,102); insert into scott.t_dept values(seq_dept.nextval,'Men lavatory men toilet','打扫男厕所分部','0'||seq_dept.nextval,112); insert into scott.t_dept values(seq_dept.nextval,'Women lavatory','打扫女厕所分部','0'||seq_dept.nextval,112); insert into scott.t_dept values(seq_dept.nextval,'PHP Development department','PHP开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'.Net Development department','.NET开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'Java Development department','Java开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'C++ Development department','C++开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'Python Development department','Python开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'Java Development department_001','Java前台开发部','0'||seq_dept.nextval,123); insert into scott.t_dept values(seq_dept.nextval,'Java Development department_002','Java后台开发部','0'||seq_dept.nextval,123); commit;
--所有t_dept表中的数据结果为：
--根据子部门查询出父部门(当prior parent = id时，数据库会跟据当前的parent来迭代出与当前的parent相同的id的记录，所以查询出来的结果就是所有的父类结果)。 select deptid, dentname_en, deptname_cn, deptcode, parent_id, level from scott.t_dept start with deptid = 109 connect by prior parent_id = deptid; 等效于: --根据子部门查询出父部门 select deptid, dentname_en, deptname_cn, deptcode, parent_id, level from scott.t_dept start with deptid = 109 connect by deptid = prior parent_id; 查询结果：
--根据父部门查询出子部门(当parent = prior id时，数据库会根据当前的id迭代出parent与该id相同的记录，所以查询的结果是迭代出了所有的子类记录)； select deptid, dentname_en, deptname_cn, deptcode, parent_id, level from scott.t_dept start with deptid = 100 connect by parent_id = prior deptid ; 等效于: --根据父部门查询出子部门 select deptid,dentname_en,deptname_cn,deptcode,parent_id,level from scott.t_dept dept start with deptid = 100 connect by prior dept.deptid = dept.parent_id; 查询结果：
--根据当前子部门查询出顶级部门ID以及部门Name：(oracle11g里面可以采用connect_by_root，非常的方便) select * from t_dept d where d.deptid = (select distinct FIRST_VALUE(deptid) OVER(ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from t_dept start with deptid = 109 connect by prior parent_id = deptid); 查询 结果：
赞 收藏 评论 分享 举报 上一篇：使用Maven命令创建Java项目和JavaWeb项目 下一篇：使用DOS命令启动本地Oracle数据库服务 |
【本文地址】
今日新闻 |
推荐新闻 |