Make the Management Tree
l Problem
The following is the Employee table of an enterprise, inclusive of the employee ID and name:
|
EMPNO |
ENAME |
|
7902 |
Ford |
|
7788 |
Scott |
|
7900 |
James |
|
7844 |
Turner |
|
7654 |
Martin |
|
7521 |
Ward |
|
7499 |
Allen |
|
7934 |
Miller |
|
7876 |
Adams |
|
7782 |
Clark |
|
7698 |
Blake |
|
7566 |
Jones |
|
7369 |
Smith |
|
7839 |
King |
The superior-subordinate relationships between employees are given in the table below. For example, the first record indicates that the manager of Employee 7902 is the Employee 7566. The 14th record indicates the Employee 7839 doesn’t have a manager because he is the highest manager.
|
EMPNO |
MGR |
|
7902 |
7566 |
|
7788 |
7566 |
|
7900 |
7698 |
|
7844 |
7698 |
|
7654 |
7698 |
|
7521 |
7698 |
|
7499 |
7698 |
|
7934 |
7782 |
|
7876 |
7788 |
|
7782 |
7839 |
|
7698 |
7839 |
|
7566 |
7839 |
|
7369 |
7902 |
|
7839 |
|
Please return a result set to describe the hierarchical relationships of the data in the table, according to the following structure:
|
EMP_TREE |
|
King |
|
King – Blake |
|
King – Blake – Allen |
|
… |
|
King – Clark |
|
King – Clark – Miller |
|
… |
l Tip
General steps: First, select out an employee A, and then find out its line manager B, and then the C that is line manager of B. After finding all these persons, run the loop until reaching the line manager that is the highest leader. Then, jump out of the inner loop, and begin a new loop to search for the managers for the next employee.
Finally, sort the resulting set and you will get the desired result.
l Code
|
|
A |
B |
C |
D |
|
|
1 |
=file("C:\\txt\\Employees2.txt").import@t() |
|
|
|
Find the employee information |
|
2 |
=file("C:\\txt\\Relationships.txt").import@t() |
|
|
|
Find the relation between employees |
|
3 |
=[] |
|
|
|
Store the subordinates relation tree having been found |
|
4 |
for A1 |
|
|
|
Loop against the employee table |
|
5 |
|
=A4.EMPNO |
|
|
Get the number of the current employee |
|
6 |
|
=A4.ENAME |
|
|
Get name of the current employee |
|
7 |
|
for |
|
|
|
|
8 |
|
|
=A2.select(EMPNO==B5).MGR |
|
Get the upper class of the current employee |
|
9 |
|
|
if C8==null |
break |
If there is no line manager of the current employee, then put an end to the inner loop, and jump to the next employee |
|
10 |
|
|
=A1.select(EMPNO==C8).ENAME |
|
Get name of the line manager of the current employee |
|
11 |
|
|
>B6=C10+"-"+B6 |
|
Add the present line manager of the employee to B6 |
|
12 |
|
|
>B5=C8 |
|
Proceed to select out the line manager’s line manager |
|
13 |
|
|
next |
|
|
|
14 |
|
>A3=A3|B6 |
|
|
Store the found hierarchy tree in the A3 |
|
15 |
=A3.sort() |
|
|
|
Sort the cell A3 and display the desired result as required in the problem |
l Result
