浅谈oracle树状结构层级查询测试数据
浅谈oracle树状结构层级查询
oracle树状结构查询即层次递归查询,是sql语句经常用到的,在实际开发中组织结构实现及其层次化实现功能也是经常遇到的,虽然我是一个java程序开发者,我一直觉得只要精通数据库那么对于java开发你就成功了三分之一,本篇中主要介绍start with...connect by prior 、order by 、sys_connect_by_path。
浅谈oracle树状结构层级查询的博客地址是 浅谈oracle树状结构层级查询之start with ....connect by prior、level及order by
这篇只是测试数据,由于全部放在一篇中显得太多,解决方案在另外一篇,博客地址:浅谈oracle树状结构层级查询
下面试测试数据:
创建表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | ( dept_id VARCHAR2(32) not null , dept_name VARCHAR2(128), dept_code VARCHAR2(32), par_dept_id VARCHAR2(32), dept_leader VARCHAR2(32), dept_desc VARCHAR2(256), create_time CHAR (19), org_id VARCHAR2(32), dept_type VARCHAR2(1), order_id NUMBER, state CHAR (1) default '1' , bqq_dept_id VARCHAR2(128), bqq_par_dept_id VARCHAR2(128) ) -- Add comments to the table comment on table SYS_DEPT is '部门信息,和单位多对一' ; -- Add comments to the columns comment on column SYS_DEPT.dept_id is '主键' ; comment on column SYS_DEPT.dept_name is '名称' ; comment on column SYS_DEPT.dept_code is '编码,用于递归' ; comment on column SYS_DEPT.par_dept_id is '父级部门ID' ; comment on column SYS_DEPT.dept_leader is '部门领导ID' ; comment on column SYS_DEPT.dept_desc is '部门描述' ; comment on column SYS_DEPT.create_time is 'yyyy-mm-dd HH:MM:SS' ; comment on column SYS_DEPT.org_id is '单位ID' ; comment on column SYS_DEPT.dept_type is '1:正式部门;2:虚拟部门(用于通讯录展示)' ; comment on column SYS_DEPT.order_id is '排序字段' ; comment on column SYS_DEPT.state is '0:无效;1:有效' ; comment on column SYS_DEPT.bqq_dept_id is '企业qqdeptid' ; comment on column SYS_DEPT.bqq_par_dept_id is '企业qq父类deptid' ; |
测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | prompt Importing table SYS_DEPT... set feedback off set define off insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '40288ac45a3c1e8b015a3c28b4ae01d6' , '客运部' , '110' , '-1' , null , null , '2017-02-14 18:26:25' , '402881e54c40d74d014c40d8407a0016' , '1' , 29, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b60f98a1d59b3' , '综合室' , '110001' , '40288ac45a3c1e8b015a3c28b4ae01d6' , null , null , '2017-04-12 15:03:38' , '402881e54c40d74d014c40d8407a0016' , '1' , 63, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b6134d9ff2946' , '生产调度' , '110001001' , '4028e4d35b5ca4ee015b60f98a1d59b3' , null , null , '2017-04-12 16:08:25' , '402881e54c40d74d014c40d8407a0016' , '1' , 135, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b60f9fae95a44' , '站务中心' , '110002' , '40288ac45a3c1e8b015a3c28b4ae01d6' , null , null , '2017-04-12 15:04:07' , '402881e54c40d74d014c40d8407a0016' , '1' , 64, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613562be2a08' , '东岗站' , '110002001' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:09:00' , '402881e54c40d74d014c40d8407a0016' , '1' , 136, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b6135f9de2aca' , '焦家湾站' , '110002002' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:09:39' , '402881e54c40d74d014c40d8407a0016' , '1' , 137, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b6136a3e22bb2' , '拱星墩站' , '110002003' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:10:22' , '402881e54c40d74d014c40d8407a0016' , '1' , 138, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613723bb2c5f' , '省气象局站' , '110002004' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:10:55' , '402881e54c40d74d014c40d8407a0016' , '1' , 139, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b6137a5772d06' , '五里铺站' , '110002005' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:11:28' , '402881e54c40d74d014c40d8407a0016' , '1' , 140, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b6137e4e72d57' , '兰州大学站' , '110002006' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:11:44' , '402881e54c40d74d014c40d8407a0016' , '1' , 141, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613840112dd0' , '东方红广场站' , '110002007' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:12:08' , '402881e54c40d74d014c40d8407a0016' , '1' , 142, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b6138765c2e12' , '省政府站' , '110002008' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:12:21' , '402881e54c40d74d014c40d8407a0016' , '1' , 143, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b6138b84b2e68' , '西关站' , '110002009' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:12:38' , '402881e54c40d74d014c40d8407a0016' , '1' , 145, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b6139390e2f06' , '文化宫站' , '110002010' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:13:11' , '402881e54c40d74d014c40d8407a0016' , '1' , 146, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613980a82f61' , '小西湖站' , '110002011' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:13:30' , '402881e54c40d74d014c40d8407a0016' , '1' , 147, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b6139c1dc2fb4' , '七里河站' , '110002012' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:13:46' , '402881e54c40d74d014c40d8407a0016' , '1' , 148, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613a24853047' , '西站十字站' , '110002013' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:14:12' , '402881e54c40d74d014c40d8407a0016' , '1' , 149, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613a81f030ce' , '兰州西站北广场站' , '110002014' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:14:36' , '402881e54c40d74d014c40d8407a0016' , '1' , 150, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613ad627313d' , '土门墩站' , '110002015' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:14:57' , '402881e54c40d74d014c40d8407a0016' , '1' , 151, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613b394c31c6' , '马滩站' , '110002016' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:15:22' , '402881e54c40d74d014c40d8407a0016' , '1' , 152, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613b9051325e' , '兰州海关站' , '110002017' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:15:45' , '402881e54c40d74d014c40d8407a0016' , '1' , 153, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613c286b332e' , '兰州城市学院(省科技馆)站' , '110002018' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:16:24' , '402881e54c40d74d014c40d8407a0016' , '1' , 154, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613c806933a3' , '深安大桥南站' , '110002019' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:16:46' , '402881e54c40d74d014c40d8407a0016' , '1' , 155, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613cdf98342c' , '陈官营站' , '110002020' , '4028e4d35b5ca4ee015b60f9fae95a44' , null , null , '2017-04-12 16:17:11' , '402881e54c40d74d014c40d8407a0016' , '1' , 157, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b60fa3e2f5a94' , '乘务中心' , '110003' , '40288ac45a3c1e8b015a3c28b4ae01d6' , null , null , '2017-04-12 15:04:24' , '402881e54c40d74d014c40d8407a0016' , '1' , 65, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613d738d34f4' , '陈官营车场组' , '110003001' , '4028e4d35b5ca4ee015b60fa3e2f5a94' , null , null , '2017-04-12 16:17:48' , '402881e54c40d74d014c40d8407a0016' , '1' , 158, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613defed359e' , '东岗车场组' , '110003002' , '4028e4d35b5ca4ee015b60fa3e2f5a94' , null , null , '2017-04-12 16:18:20' , '402881e54c40d74d014c40d8407a0016' , '1' , 159, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613e42ae3612' , '第一车队' , '110003003' , '4028e4d35b5ca4ee015b60fa3e2f5a94' , null , null , '2017-04-12 16:18:41' , '402881e54c40d74d014c40d8407a0016' , '1' , 161, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613e7a50366c' , '第二车队' , '110003004' , '4028e4d35b5ca4ee015b60fa3e2f5a94' , null , null , '2017-04-12 16:18:56' , '402881e54c40d74d014c40d8407a0016' , '1' , 162, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613ebc8e36c1' , '第三车队' , '110003005' , '4028e4d35b5ca4ee015b60fa3e2f5a94' , null , null , '2017-04-12 16:19:13' , '402881e54c40d74d014c40d8407a0016' , '1' , 163, '1' , null , null ); insert into SYS_DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE, PAR_DEPT_ID, DEPT_LEADER, DEPT_DESC, CREATE_TIME, ORG_ID, DEPT_TYPE, ORDER_ID, STATE, BQQ_DEPT_ID, BQQ_PAR_DEPT_ID) values ( '4028e4d35b5ca4ee015b613eff483729' , '第四车队' , '110003006' , '4028e4d35b5ca4ee015b60fa3e2f5a94' , null , null , '2017-04-12 16:19:30' , '402881e54c40d74d014c40d8407a0016' , '1' , 164, '1' , null , null ); prompt Done. |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(三):用.NET IoT库
· 【非技术】说说2024年我都干了些啥