SQL Server 2000 supports tree-like table Query

Tree structure is an important non-linear structure. In relational databases, it is a common problem to query tables with a tree structure to obtain the required data.
Taking SQL Server 2000 as an example, the author provides algorithms and code for some common queries, which is worthy of your reference.

Relational databases organize data in the form of tables. It is convenient and flexible to Process Tables and is easy to learn and use. Therefore, it is widely used. The tables processed by relational databases are linear

Each row of the table corresponds to a data element, which is called a record. Records are linearly arranged and there is no connection between them. However, when solving actual problems,
Data with non-linear structures is often encountered. As shown in the following table, the upper-level code in each record is associated with other records, forming a layered tree,
It can be represented in the following figure:

A tree structure is a structure with branches and hierarchical relationships between nodes. It is very similar to a tree in nature. Tree structures exist in a large number in the objective world, such as genealogy,
All administrative organizations can be represented in a tree. Tree is also widely used in the computer field. For example, in compiling a program, a tree is used to represent the syntax structure of the source program;
In the database system, the tree is used to organize information. When analyzing algorithm behavior, the tree is used to describe the execution process.
In relational databases, it is a common requirement to query tables with a tree structure to obtain the required data. The following uses sqlserver 2000 as an example,
The following algorithms and codes are provided for three common queries:

1. node A's parent node information at Layer N, such as the name of employee Huang Jingjing's superior of the previous two levels.
2. Statistical information of a subtree, such as Yu shunjing, an employee, and the total salary of all its employees.
3. node information of a subtree, such as the name of employee Zheng Coco and all its employees.

Parent node information of a node
 To implement such a query, recursive methods are often used. We can use the User-Defined Function (UDF, user-defined function) added by SQL Server 2000)

This new feature is used to call recursive functions. The following is a function definition:

Create Function DBO. getmanager (@ employee_idas char (5), @ level as Int = 1 -- the default value is 1)
 Returns char (5)

Here, employee_id indicates the employee number to be queried, level indicates the number of employees higher than the employee's level, and the returned result is the employee number of the boss.
The recursion of this function is defined:
 If level = 0, the current employee number is returned;
 If the level is greater than 0, the system returns the level-1 Superior Number of the direct superior.
Based on this recursive definition, we can write a complete recursive function:
Create Function DBO. getmanager (@ employee_id as char (5), @ level asint = 1) returns char (5)
As
Begin
 If @ level = 0
    Return @ employee_id
 -- If the level is 0, the superior number has been found.
 Return DBO. getmanager (select [Superior Number] from [employee information] Where [employee number] = @ employee_id ),
   @ Level-1) -- if the level is greater than 0, return the level-1 Superior Number of the direct superior.
End
Run the following statement to obtain the expected result:
Select * from [employee information] Where [employee number] = DBO. getmanager ('e9907', 2)

Of course, if we want to make this recursive function more robust, we also need to add a fault tolerance check to the function, which will not be described here.

Statistical information of a subtree
 This query is also implemented using recursive methods. Let's take a look at the function definition:
 Create Function DBO. gettotalsalary (@ manager_idas char (5) returns int
Here, @ manager_id is the employee number of a boss to be counted and returns the total salary of all its subordinates.
 The recursion of this function is defined as: If there are no subordinates, the current salary is returned; if there are subordinates, the total salary of all subordinates is returned.
Based on this recursive definition, we can write a complete recursive function:
Create Function DBO. gettotalsalary (@ manager_id as char (5) returns int
Begin
Return (select [salary] from [employee information] Where
 [Employee number] = @ manager_id) +
   CaseWhen exists (select * from [employee information] Where [Superior Number] = @ manager_id)
       Then (select sum (DBO. gettotalsalary ([employee number]) from [employee information] Where [Superior Number] = @ manager_id)
   Else 0
End
End

The above user-defined function uses the case search function. It calculates the value of boolean_expression for each when clause in the specified order and returns the first value

Result_expression of true boolean_expression. If no boolean_expression value is set to true, an else clause is available.

SQL Server Returns else_result_expression; if there is no else clause, return null.

In the Custom User Function, if the employee information table finds that the employee has a subquery (exists subquery), The gettotalsalary function is called for each subquery to return the total salary of the subordinate employee.

And use the sum function to sum the sum. Otherwise, the salary is directly returned.

Run the following statement to obtain the required results:
 Select DBO. gettotalsalary ('e9902') as 'total sales'
In actual work, there may also be such query requirements, that is, the total number of subordinate levels (including their own) of an employee, for example, Zhang Jianping has a total of four subordinate levels. Use the tree term to describe,

That is, the depth of a subtree is obtained. This can be achieved through recursive functions:

Create Function DBO. getunderlyinglevel (@ manager_id as char (5) returns int
Begin
 Return
   Case whenexists (select * from [employee information] Where [Superior Number] = @ manager_id)
        Then 1 + (select max (DBO. getunderlyinglevel ([employee number]) from [employee information] Where [Superior Number] =    

        @ Manager_id)
   Else 1
   End

End

Run the following statement to obtain the required results:
Select DBO. getunderlyinglevel ('e9901 ') as 'subordinate level'
Information about all subnodes of a subtree
Both of the preceding queries return scalar values. Here, the query must return the information of all the subnodes of a subtree. This is a result set and needs to be stored in the table data type. Letter

The number is defined as follows:
Create Function DBO. getsubtreeinfo (@ manager_id as INT)
Returns @ treeinfo table
([Employee number] [char] (5) not null,
[Name] [char] (10) Not null,
[Age] [int] not null,
[Salary] [money] not null,
[Superior number] [char] (5) null,
[Level] [int] not null)
Here, @ manager_id indicates the employee number of the supervisor to be queried, and the information of all its subordinates is returned. The information is stored in the table variable @ treeinfo.
Because the query returns a result set, it cannot be implemented using recursive methods. We use the loop method. The loop process is to set the parameter @ manager_id.

The information of the representative supervisor is inserted into the table, and the Level 0 is granted; the level is increased to 1, and all the employee information of the superior number above @ manager_id is inserted into the table; the level is increased to 2

, Insert all employee information of the same employee number as the employee number in the record inserted in step 2 to the table; Add the level in sequence until the Superior Number is not found and the employee number in the previous step is inserted

Until the employee information is consistent with the employee number.
To implement this loop, we need to use the system function @ rowcount to determine whether new records in the previous step are inserted into the table. If yes, the loop continues. If no, the loop continues.

End. In addition, a field named "level" is added to the table to show the level relationship and represent each newly inserted record.

. The complete function definition is as follows:
Create Function DBO. getsubtreeinfo (@ manager_id as char (5 ))
 Returns @ treeinfo table
([Employee number] [char] (5) not null,
  [Name] [char] (10) notnull,
  [Age] [int] not null,
  [Salary] [money] not null,
  [Superior number] [char] (5) null,
  [Level] [int] not null
 )
Begin
Declare @ level as int
Select @ level = 0
Insert into @ treeinfo
Select [employee number], [name], [age], [salary], [Superior Number], @ level
From [employee information]
Where [employee number] = @ manager_id
While @ rowcount> 0
Begin
  Set @ level = @ LEVEL + 1
  Insert into @ treeinfo
  Select E. [employee number], E. [name], E. [age], E. [salary], E. [Superior Number], @ level
    From [employee information] As e join @ treeinfo as t
    On E. [Superior Number] = T. [employee number] and T. [level] = @ level-1
End
Return
End
The test result is as follows:
Select * From DBO. getsubtreeinfo ('e9903 ')
Employee number name age salary Superior Number level
--------------------------
E9903 Zheng Coco 38 5000.0000 e9901 0
E9906 Xiao Yao 26 3350.0000 e9903 1
E9907 Huang Jingjing 22 2800.0000 e9906 2

Finally, let's look at an interesting example. After slightly modifying the preceding function, you can print the tree structure in a graphical manner. The result is as follows:

The complete functions are as follows:
Create Function DBO. getsubtreeinfo2 (@ manager_id as char (5) returns @ treeinfo table
 ([Employee number] [char] (5) not null,
   [Name] [char] (10) Not null,
   [Age] [int] not null,
   [Salary] [money] not null,
   [Superior number] [char] (5) null,
   [Level] [int] not null,
   [Tag] [varchar] (200) not null
 )
Begin
Declare @ level as int, @ path as varchar (200)
Select @ level = 0, @ Path = 'null'
Insert into @ treeinfo
Select [employee number], [name], [age], [salary], [Superior Number], @ level, 'null-> '+ [employee number]
From [employee information]
Where [employee number] = @ manager_id
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert into @ treeinfo
Select E. [employee number], E. [name], E. [age], E. [salary], E. [Superior Number], @ level, T. [Mark] + '->' + E. [employee number]
From [employee information] As e join @ treeinfo as t
Privilege on E. [Superior Number] = T. [employee number] and T. [level] = @ level-1
End
Return
End
Use the following statement to return the tree structure shown above:
Select replicate ('|', [level]) + [name] as organizational structure fromdbo. getsubtreeinfo2 ('e9901 ') order by [tag]

 

Create this table by yourself and try it !!

Reference:

Http://blog.sina.com.cn/s/blog_4af2201a01008d6p.html