基本介绍

在存有父子关系的表中，避免不了根据父子关系，递归查询所有层级的业务逻辑。基于这样的业务需求，Halo数据库主要使用start with和connect by来实现层次查询。对于复杂的多表递归场景，我们来进行如下实践。

数据准备

CREATE TABLE employees_l (  employee_id int4 NULL,  full_name varchar NULL,  temp_id int4 NULL);CREATE TABLE employees_r2 (  temp_id2 int4 NULL,  manager_id int4 NULL);

插入数据

INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(1, 'Michael North', 1);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(2, 'Megan Berry', 2);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(3, 'Sarah Berry', 3);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(4, 'Zoe Black', 4);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(5, 'Tim James', 5);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(6, 'Bella Tucker', 6);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(7, 'Ryan Metcalfe', 7);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(8, 'Max Mills', 8);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(9, 'Benjamin Glover', 9);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(10, 'Carolyn Henderson', 10);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(11, 'Nicola Kelly', 11);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(12, 'Alexandra Climo', 12);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(13, 'Dominic King', 13);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(14, 'Leonard Gray', 14);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(15, 'Eric Rampling', 15);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(16, 'Piers Paige', 16);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(17, 'Ryan Henderson', 17);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(18, 'Frank Tucker', 18);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(19, 'Nathan Ferguson', 19);INSERT INTO employees_l (employee_id, full_name, temp_id) VALUES(20, 'Kevin Rampling', 20);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(1, NULL);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(2, 1);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(3, 1);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(4, 1);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(5, 1);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(6, 2);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(7, 2);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(8, 2);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(9, 2);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(10, 3);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(11, 3);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(12, 3);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(13, 3);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(14, 4);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(15, 4);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(16, 7);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(17, 7);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(18, 8);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(19, 8);INSERT INTO employees_r2 (temp_id2, manager_id) VALUES(20, 8);

业务实践

根据员工号=》经理号的归属关系，递归查询2张表所有的上下级关系。

SELECT  LEVEL,EMPLOYEE_ID aaa,el.FULL_NAME ,el.TEMP_ID ,er.TEMP_ID2 ,  sys_connect_by_path(el.EMPLOYEE_ID , ','), (prior el.full_name) mgrna  from EMPLOYEES_L el JOIN EMPLOYEES_R2 er ON el.TEMP_ID =er.TEMP_ID2 start with el.employee_id=1connect BY prior  el.employee_id = er.MANAGER_ID;

总结

start with和connect by是halo中提供的一种递归的机制，但是我们应该避免发生递归的死循环，也就是数据的环状，否则会消耗大量资源直到数据库异常出错。合理地使用层次查询，能够高效地满足复杂的递归需求。

Halo数据库的层次查询