Shulou(Shulou.com)06/01 Report--

Today, Brother Qiang is going to share with you the knowledge of self-connection in the database.

In the database design, how to express the superior and subordinate relationship of employees, and how to express the relationship between employees and subordinates? How to express the relationship between subordinates and subordinates? How is the relationship between parent and child nodes represented in the tree menu structure? In the forum post, how to express the relationship between the main post and the reply post?

These three relationships are all related to the self-connection of tables, so let's explain and analyze the relationship between employees' superiors and subordinates.

For employee superior-subordinate relationship, many database beginners may put the data in two tables, one table for superior employees and the other for subordinate employees, and set foreign keys to associate the two tables. If you deal with it this way, imagine that if an employee has both superiors and subordinates, should the employee appear in the superior table or the subordinate table? This is a very complicated question.

In practical work, we put our superiors and subordinates in one table. This superior-subordinate relationship is clearly expressed in a table. For example, Tang Monk is a leader. He has two subordinates, one is Sun WuKong, the other is Zhu Bajie. Sun WuKong has a subordinate named Monkey Xiaoqi and Zhu Bajie has a subordinate called Pig Xiaoer, so the data table should be designed like this.

Employee information table employee

Analyze this table, the father serial number of the Tang monk is 0, and there is no data with the serial number 0 in the table, so the Tang monk has no superior. The father serial number of Sun WuKong and Zhu Bajie is 1, and the serial number of Tang monk is 1, so the superiors of Sun WuKong and Zhu Bajie are both Tang monks. Monkey Xiaoqi's father serial number is 2, and Sun WuKong's serial number is 2, so the superior of Monkey Xiaoqi is Sun WuKong. The father serial number of the pig waiter is 3, while that of Zhu Bajie is 3, so the superior of the pig waiter is Zhu Bajie.

Now that you understand the data on how to organize this father-son relationship in the same table, let's talk about how to get the name of someone's direct subordinate through the SQL command.

For example, who are the subordinates of the Tang monk? If there is a lot of data in the table, it can't be seen at a glance. And we can see that it is useless, when we do software development, we need to extract it through the SQL command and use it in a specific place in the program.

We should all know how to query the data in the two tables. The associated query of a table's own data is a little more complicated, which is the self-join query that Qiang Brother is going to tell you about here. In order to illustrate this example, I drew the following picture. In this picture, the previous table is treated as two tables, aliased as an and b, respectively.

Self-connection analysis legend

Through this table, we can easily see that if we want to find the names of all the subordinates of the Tang monk, we should connect the data whose father serial number in table b is equal to the serial number in table a, and find the data whose name is Tang monk in table a from the connection result. Based on this analysis, assuming that the name of the table in the database is employee, you can write the following SQL command.

Select b.name from employee a, employee b where a.id=b.pid and a.namefang 'Tang monk'

To find someone else's subordinate, you just need to change the name. Table self-join query is the basic skill of data design, and everyone must master it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.