Employees2.txt is an employee table for a certain company, which records the ID and name of each employee:

Relationships.txt records the subordinate relationships between employees. For example, the first record represents employee 7902, whose manager is employee 7566, and the 14th record represents employee 7839, who has no manager and is the top-level manager.

Please return a result set that describes the hierarchical relationship of the entire table, with the following structural requirements:

King

King-Blake

King-Blake-Allen

…

King-Clark

King-Clark-Miller

…

Firstly, identify employee A and find their corresponding superior B through the relationship table. Then, search for B's superior C and repeat the process. Until the superior is already the highest leader, break out of the inner loop and search for the next employee's superior in a loop.

Finally, sort the results to obtain the desired result for the question.

http://try.scudata.com.cn/try.jsp?splx=ExB003lcgljgs1.splx

A1 reads employee table and specifies EMPNO as the primary key, while B1 reads the relationship table and also sets the primary key. C1 has prepared an empty sequence for storing the results.

A2 loops the employee table. B2 and C2 retrieve the current employee's EMPNO and name. B3 starts a loop to search for his superior, C3 finds his immediate superior in the relationship table, and if there is no superior, the search is completed and the loop ends. If the immediate superior is found, C5 will find the superior's name in the employee table, add the superior's name before the employee’s name in C2, and update the EMPNO in B2 before continuing to search for the superior's supervisor. If the search is complete, record a result in C1.

After searching for the information of all employees, A8 will sort the results according to the requirements and return:

You can also use the join calculation in SPL to obtain results more conveniently:

http://try.scudata.com.cn/try.jsp?splx=ExB003lcgljgs2.splx

A1 and A2 read out the employee table and relationship table, A3 joins the two tables and adds his superior number MGR to the employee table fields.

A4 uses foreign key association calculation to switch the MGR in A3 to the corresponding record. In SPL, the switch function can be used to perform foreign key association, switching foreign key fields to corresponding dimension table records to implement real association, making calculations more intuitive and convenient. The results in A3 are as follows:

The color displayed in MGR is different from EMPNO because it has been converted into the corresponding record, which can be viewed by double clicking. For example, double clicking 7698 can view the data of the corresponding EMPNO:

Double click on the MGR field to continue viewing his immediate superior.

A5 loops to calculate the superior information of each employee. During the calculation, the prior function is used to search for his MGR at all levels until it cannot be found, and extract their ENAMEs. At this point, the results obtained are from low to high levels. Therefore, the rvs function needs to be used to arrange them in reverse order, and concat can be used to obtain the required hierarchical relationship string. The result obtained after sorting is consistent with the previous method.