- 创建树结构表
--部门目录结构表 CREATE TABLE BAI_ORG ( BAI_ORG_ID NUMBER PRIMARY KEY , --部门ID BAI_ORG_NAME VARCHAR2(225), --部门名字 BAI_ORG_P_ID NUMBER , --部门父级级部门ID BAI_ORG_LEAD VARCHAR2(225), --部门领导 BAI_ORG_ROOT_ID NUMBER --部门顶级父部门ID ); --顶级根部门 Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(1 , '顶级根部门1', Null, Null , Null); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(2 , '顶级根部门2', Null, Null , Null); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(3 , '顶级根部门3', Null, Null , Null); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(4 , '顶级根部门4', Null, Null , Null); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(5 , '顶级根部门5', Null, Null , Null); --一级部门 Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(6 , '一级部门6', 1, '张三', 1); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(7 , '一级部门7', 1, '李四', 1); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(8 , '一级部门8', 1, '王五', 1); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(9 , '一级部门9', 2, '刘六', 2); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(10, '一级部门10', 2 , '田七', 2); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(11, '一级部门11', 2 , '陈八', 2); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(12, '一级部门12', 3 , '陈八', 3); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(13, '一级部门13', 3 , '田七', 3); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(14, '一级部门14', 3 , '刘六', 3); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(15, '一级部门15', 4 , '王五', 4); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(16, '一级部门16', 4, '李四', 4); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(17, '一级部门17', 4 , '张三', 4); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(18, '一级部门18', 5 , '陈八', 5); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(19, '一级部门19', 5 , '刘六', 5); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(20, '一级部门20', 5 , '田七', 5); --二级菜单 Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(21, '二级菜单21', 6 , '张三', 1); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(22, '二级菜单22', 7 , '李四', 1); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(23, '二级菜单23', 8 , '王五', 1); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(24, '二级菜单24', 9 , '张三', 2); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(25, '二级菜单25', 10, '田七', 2); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(26, '二级菜单26', 11, '刘六', 2); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(27, '二级菜单27', 12, '王八', 3); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(28, '二级菜单28', 13, '陈八', 3); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(29, '二级菜单29', 14, '田七', 3); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(30, '二级菜单30', 15, '张三', 4); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(31, '二级菜单31', 16, '李四', 4); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(32, '二级菜单32', 17, '李四', 4); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(33, '二级菜单33', 18, '王五', 5); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(34, '二级菜单34', 19, '王五', 5); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(35, '二级菜单35', 20, '田七', 5); --三级菜单 Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(36, '二级菜单36', 21, '张三', 1); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(37, '二级菜单37', 22, '张三', 1); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(38, '三级菜单38', 23, '陈八', 1); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(39, '三级菜单39', 24, '刘六', 2); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(40, '三级菜单40', 25, '陈八', 2); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(41, '三级菜单41', 26, '刘六', 2); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(42, '三级菜单42', 27, '李四', 3); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(43, '三级菜单43', 28, '张三', 3); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(44, '三级菜单44', 29, '李四', 3); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(45, '三级菜单45', 30, '王五', 4); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(46, '三级菜单46', 31, '张三', 4); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(47, '三级菜单47', 32, '王五', 4); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(48, '三级菜单48', 33, '张三', 5); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(49, '三级菜单49', 34, '王五', 5); Insert Into BAI_ORG(BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID) Values(50, '三级菜单50', 35, '陈八', 5); |
-
说明:
ORACLE 树查询的主要语法: SELECT ... START WITH ... CONNECT BY ... PRIOR |
2.部门结构树操作
2.1 查找树中的所有顶级父节点。
SELECT * FROM BAI_ORG WHERE BAI_ORG_P_ID IS NULL OR BAI_ORG_P_ID = 0; |
2.2 查找一个节点的直属子节点(所有儿子)
SELECT BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_ROOT_ID FROM BAI_ORG WHERE BAI_ORG_P_ID = 1 |
2.3 查找一个节点的所有直属子节点(所有后代)
SELECT BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_ROOT_ID FROM BAI_ORG START WITH BAI_ORG_ID = 1 CONNECT BY BAI_ORG_P_ID = PRIOR BAI_ORG_ID; |
2.4 查找一个节点的直属父节点(父亲)。
SELECT B.BAI_ORG_ID, B.BAI_ORG_NAME, B.BAI_ORG_P_ID, B.BAI_ORG_LEAD, B.BAI_ORG_ROOT_ID FROM BAI_ORG A JOIN BAI_ORG B ON A.BAI_ORG_P_ID = B.BAI_ORG_ID WHERE A.BAI_ORG_ID = 18 OR B.BAI_ORG_ID = 18 ORDER BY B.BAI_ORG_ID; |
2.5 查找一个节点的所有直属父节点(祖宗)
SELECT M.BAI_ORG_ID, M.BAI_ORG_NAME, M.BAI_ORG_P_ID, M.BAI_ORG_LEAD, M.BAI_ORG_ROOT_ID FROM BAI_ORG M START WITH M.BAI_ORG_ID = 38 CONNECT BY PRIOR M.BAI_ORG_P_ID = M.BAI_ORG_ID ORDER BY M.BAI_ORG_ID; |
- 注意:
上面列出两个树型查询方式,第2.3条语句和第2.5条语句,这两条语句之间的区别在于PRIOR关键字的位置不同,所以决定了查询的方式不同。 当BAI_ORG_P_ID = PRIOR BAI_ORG_ID时,数据库会根据当前的BAI_ORG_ID迭代出BAI_ORG_P_ID与该BAI_ORG_ID相同的记录,所以查询的结果是迭代出了所有的子类记录;而PRIOR BAI_ORG_ID = BAI_ORG_P_ID时,数据库会跟据当前的BAI_ORG_P_ID来迭代出与当前的BAI_ORG_P_ID相同的BAI_ORG_ID的记录,所以查询出来的结果就是所有的父类结果。
- 补充说明:
对于数据库来说,根节点并不一定是在数据库中设计的顶级节点,对于数据库来说,根节点就是START WITH开始的地方。
2.6 查询一个节点的兄弟节点(亲兄弟)。
SELECT A.BAI_ORG_ID, A.BAI_ORG_NAME, A.BAI_ORG_P_ID, A.BAI_ORG_LEAD, A.BAI_ORG_ROOT_ID FROM BAI_ORG A WHERE EXISTS (SELECT * FROM BAI_ORG B WHERE A.BAI_ORG_P_ID = B.BAI_ORG_P_ID AND B.BAI_ORG_ID = 37); |
2.7 查询与一个节点同级的节点(族兄弟)。
WITH TMP AS (SELECT A.BAI_ORG_ID, A.BAI_ORG_NAME, A.BAI_ORG_P_ID, A.BAI_ORG_LEAD, A.BAI_ORG_ROOT_ID, LEVEL LEV FROM BAI_ORG A START WITH A.BAI_ORG_P_ID IS NULL CONNECT BY A.BAI_ORG_P_ID = PRIOR A.BAI_ORG_ID) SELECT * FROM TMP WHERE LEV = (SELECT LEV FROM TMP WHERE BAI_ORG_ID = 50); |
- 说明:
这里使用两个技巧,一个是使用了LEVEL来标识每个节点在表中的级别,还有就是使用with语法模拟出了一张带有级别的临时表。
2.8 查询一个节点的父节点的的兄弟节点(伯父与叔父)。
WITH TMP AS (SELECT B.BAI_ORG_ID, B.BAI_ORG_NAME, B.BAI_ORG_P_ID, B.BAI_ORG_LEAD, B.BAI_ORG_ROOT_ID, LEVEL LEV FROM BAI_ORG B START WITH BAI_ORG_P_ID IS NULL CONNECT BY BAI_ORG_P_ID = PRIOR BAI_ORG_ID ORDER BY BAI_ORG_ID) SELECT B.* FROM TMP B, (SELECT * FROM TMP WHERE BAI_ORG_ID = 21 AND LEV = 2) A WHERE B.LEV = 1 UNION ALL SELECT * FROM TMP WHERE BAI_ORG_P_ID = (SELECT DISTINCT GP.BAI_ORG_ID FROM TMP GP, --祖父(Grandparent) TMP P, --父亲(Parent) (SELECT * FROM TMP WHERE BAI_ORG_ID = 21 AND LEV > 2) S --儿子(Son) WHERE P.BAI_ORG_ID = S.BAI_ORG_P_ID AND GP.BAI_ORG_ID = P.BAI_ORG_P_ID); |
2.8.1这里查询分成以下几步。
首先,和第2.7的一样,将全表都使用临时表加上级别;其次,根据级别来判断有几种类型,以上文中举的例子来说,有三种情况:
- 当前节点为顶级节点,即查询出来的lev值为1,那么它没有上级节点,不予考虑。
-
当前节点为2级节点,查询出来的lev值为2,那么就只要保证lev级别为1的就是其上级节点的兄弟节点。
-
其它情况就是3以及以上级别,那么就要选查询出来其上级的上级节点(祖父),再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。最后,就是使用union将查询出来的结果进行结合起来,形成结果集。
2.9 查询一个节点的父节点的同级节点(族叔)。
WITH TMP AS (SELECT B.BAI_ORG_ID, B.BAI_ORG_NAME, B.BAI_ORG_P_ID, B.BAI_ORG_LEAD, B.BAI_ORG_ROOT_ID, LEVEL LEV FROM BAI_ORG B START WITH B.BAI_ORG_P_ID IS NULL CONNECT BY B.BAI_ORG_P_ID = PRIOR B.BAI_ORG_ID) SELECT * FROM TMP WHERE LEV = (SELECT LEV FROM TMP WHERE BAI_ORG_ID = 6) - 1; |
2.10 名称要列出名称全部路径。
--从顶部开始: SELECT SYS_CONNECT_BY_PATH (BAI_ORG_NAME, '/') FROM BAI_ORG WHERE BAI_ORG_ID = 50 START WITH BAI_ORG_P_ID IS NULL CONNECT BY BAI_ORG_P_ID = PRIOR BAI_ORG_ID;
--从当前节点开始:
SELECT SYS_CONNECT_BY_PATH (BAI_ORG_NAME, '/') FROM BAI_ORG START WITH BAI_ORG_ID = 50 CONNECT BY PRIOR BAI_ORG_P_ID = BAI_ORG_ID; |
- 在上面的例子中,第一个SQL是从根节点开始遍历,而第二个SQL是直接找到当前节点,从效率上来说已经是千差万别,更关键的是第一个SQL只能选择一个节点,而第二个SQL却是遍历出了一颗树来。
- SYS_CONNECT_BY_PATH函数就是从START WITH开始的地方开始遍历,并记下其遍历到的节点,START WITH开始的地方被视为根节点,将遍历到的路径根据函数中的分隔符,组成一个新的字符串。
2.11 列出当前节点的根节点。
-
说明:根节点就是START WITH开始的地方,CONNECT_BY_ROOT函数用来列的前面,记录的是当前节点的根节点的内容
SELECT CONNECT_BY_ROOT BAI_ORG_NAME, BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID FROM BAI_ORG START WITH BAI_ORG_ID = 50 CONNECT BY PRIOR BAI_ORG_P_ID = BAI_ORG_ID; |
2.12 列出当前节点是否为叶子。
SELECT CONNECT_BY_ISLEAF, BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID FROM BAI_ORG START WITH BAI_ORG_P_ID IS NULL CONNECT BY BAI_ORG_P_ID = PRIOR BAI_ORG_ID; |
-
CONNECT_BY_ISLEAF函数用来判断当前节点是否包含下级节点,如果包含的话,说明不是叶子节点,这里返回0;反之,如果不包含下级节点,这里返回1。
2.13 假设存在公司级管理员和非公司级管理员的角色,那么进行以下查询(备注:非公司级管理员,以当前管理员过滤查询,公司级管理员不过滤查询,BAI_ORG_ROOT_ID记录树结构的数据的顶级节点ID)
2.13.1“张三”非公司级管理员进行查询顶级部门(BAI_ORG_ROOT_ID=4)下他的管理部门
SELECT BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID FROM BAI_ORG WHERE BAI_ORG_ID IN (SELECT BAI_ORG_ID FROM (SELECT B.BAI_ORG_ID, B.BAI_ORG_P_ID FROM BAI_ORG B WHERE B.BAI_ORG_ROOT_ID = 4) A START WITH A.BAI_ORG_ID IN (SELECT BAI_ORG_ID FROM BAI_ORG WHERE BAI_ORG_ROOT_ID = 4 AND BAI_ORG_LEAD = '张三') CONNECT BY PRIOR A.BAI_ORG_P_ID = A.BAI_ORG_ID) ORDER BY BAI_ORG_ID |
2.13.2 “张三”为公司级管理员进行查询顶级部门(BAI_ORG_ROOT_ID=4)下的管理部门
SELECT BAI_ORG_ID, BAI_ORG_NAME, BAI_ORG_P_ID, BAI_ORG_LEAD, BAI_ORG_ROOT_ID FROM BAI_ORG WHERE BAI_ORG_ID IN (SELECT BAI_ORG_ID FROM (SELECT B.BAI_ORG_ID, B.BAI_ORG_P_ID FROM BAI_ORG B WHERE B.BAI_ORG_ROOT_ID = 4) A START WITH A.BAI_ORG_ID IN (SELECT BAI_ORG_ID FROM BAI_ORG WHERE BAI_ORG_ROOT_ID = 4 --AND BAI_ORG_LEAD = '张三' ) CONNECT BY PRIOR A.BAI_ORG_P_ID = A.BAI_ORG_ID) ORDER BY BAI_ORG_ID |
- 注意:
以上查询是我公司存在的查询,BAI_ORG_ROOT_ID是表结构设计的一个技巧,及优化数据,又方便了查询;BAI_ORG_ROOT_ID 是记录树结构表的顶级节点唯一识别ID