Realize the query with SQL
■ Happy
The tree structure is an important nonlinear structure, how to query the tree structure in the relational database, so that the required data is a common problem. The author uses SQL Server 2000 as an example, there is a corresponding algorithm and code for some common queries, quite a reader to learn from the reader. Tree structure relational database The data is organized in form. It has convenient and flexible processing, and it is easy to use, and it is widely used. The form processed by the relational database is linear structure, and each row of the table corresponds to a data element, called a record. The record and records are linearly arranged in linear, and there is no connection between each other, however, data in nonlinear structures are often encountered when solving practical problems. As shown in the following table, the superiors in each record are linked to other records, which form a hierarchical tree, which can be imaged by the following figure: tree structure is a junction There is a branch between the points and have a hierarchical structure, which is very similar to the tree in nature. Tree structure existently existed in the objective world, such as family spectrum, and administrative organizations can be represented by trees. The tree also has a wide range of applications in the computer field, such as in the compiler, with a tree to represent the syntax structure of the source program; in the database system, the tree is organized; when the behavior of the analysis algorithm, use the tree to describe it. Implementation process. How to query the table with a tree structure in the relational database, resulting in a common requirement. Below SQL Server 2000 as an example, the corresponding algorithms and code are given in three common queries: 1. The node A is the name information of the Nord N-layer, such as: the name of the secondary boss of Huang Jingjing. 2. Statistics of a tree, such as: employee Yu Shijing and all its subsidiaries of employees. 3. The node information of a subtree, such as employee Zheng Ke, and all its subsidiaries. The parent node information of a node is to implement such queries, often use the recursive method. We can use the new features of the user-defined function (UDF, User Defined function) added by SQL Server 2000 to implement recursive function calls. The following is the definition of the function: create function dbo.getManager (@EMPLOYEE_ID AS Char (5), @LEVEL AS INT = 1 - Default is 1) Returns Char (5) Where, Employee_ID means that the employee number to be queried, Level Indicates the number of levels above the employee, the result of the return is the employee number of the boss. The recursive definition of this function is that if level = 0, returns the current employee number; if level> 0, return the direct boss's Level-1 boss number.
According to such recursive definition, we can write a complete recursive function: create function dbo.getManager (@EMPLOYEE_ID As Char (5), @LEVEL AS INT = 1) Returns Char (5) AS Begin if @LesL = 0 return @ EMPLOYEE_ID - If Level is 0, it indicates that it has found its boss number return dbo.getManager (SELECT [superior number] from [employee information] WHERE [employee number] = @Employee_id), @LEVEL -1) - If Level is greater than 0, returning to the direct boss's Level-1 boss End executes the following statement to get the result: SELECT * FROM [employee information] WHERE [employee number] = dbo.getManager ('E9907', 2) Of course, If you want to make the recursive function more robust, we also need to add fault-tolerant check in the function, and will not be described here. The statistics of a sub-tree This query is also implemented using the recursive method. Let's take a look at the function definition: create function dbo.gettotalsalary (@Manager_id as char (5)) Returns Int, @manager_id is the employee number of a certain boss to count, returning to all of its subsidiaries. The recursive definition of this function is: If there is no subordinate, return the current payment; if there is a subordinate, return all subordinates. Based on such recursive definition, we can write a complete recursive function: create function dbo.gettotalsalary (@Manager_id as char (5)) Returns int as begin Return (SELECT [pay] from [employee information] WHER [employee number] = @Manager_id) Case When Exists (SELECT * FROM [Employee Information] Where [Master Number] = @Manager_ID) The (SELECT SUM (DBo.gettotalsalary ([Employee Number])) from [Employee Information] Where [superior number] = @manager_id) Else 0 End End Using the Case search function in the custom user function above, it evaluates the boolean_expression for each when the child sentence in the specified order, returns the first Boolean_expression of True, if not The value of true_expression is taken, and the SQL Server will return ELSE_RESULT_EXPRESSION when there is an else clause; if there is no ELSE clause, the NULL value is returned. In the custom user function, if the employee is found in the employee information table (Excents sub-query), the GetTotalsalary function returns to the total salary of the subordinates for each subordinating, and uses the SUM function to summarize; amount. Execute the following statement to get the required result: Select DBo.gettotalsalary ('E9902') AS 'The actual work may have such a query requirement, that is, a total of some employees, how many subordinate levels (including itself) If Zhang Jianping has four subordinate levels.
Terms with trees are described, that is, the depth of a subtree is obtained. You can achieve this recursive function: create function dbo.getunderlyingLevel (@manager_id as char (5)) Returns int as begin return case when EXISTS (SELECT * FROM [employee information] Where [superior number] = @Manager_id) THEN 1 (SELECT MAX (DBO.GETUNDERLYINGLEVEL ([employee number])) from [employee information] Where [upper number] = @Manager_id) ELSE 1 End End Execute the following statement to get the desired result: Select DBo.getUnderlyingLevel (' E9901 ') AS' Sub-Level 'The two queries of a sub-tree all the two queries have returned to the scalar value, where the query needs to return all child nodes of a sub-tree, this is a result set, need Store with the Table data type. The function 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, [Wages] [Money] NOT NULL, [Month Number] [CHAR] [25) NULL, [Level] [INT] NOT NULL) Among them, @manager_id represents the employee number of the boss to query, return is All subordinate information, this information is stored in the Table type variable @treeInfo. Since the query returns a result set, the recursive method is not implemented, we use the loop method to implement, the loop process is: Insert the information of the boss represented by the parameter @Manager_ID into the table, grace the level 0; Level is added to 1, insert all the employee information of all the upper number to the @manager_id into the table; the level is increased to 2, and the employee information consistent with the employee number in the record in the second step inserted into the table. In order to increase the level in turn until the employee information consistent with the employee number in the previously inserted record. In order to implement this loop, we must use the system function @@ rowcount to determine if there is a new record in the previous step being inserted into the table. If there is, the loop continues; if there is no, the loop ends. In addition, we add a field called "Level" in the table, which can display the level relationship, can also be used to represent each newly inserted record, which can be described as two.
The complete function is defined as follows: create function dbo.getsubtreeInfo (@Manager_id as char (5)) Returns @treeinfo table ([employee number] [char] (5) Not null, [Name] [char] (10) Not null, [Age] [INT] NOT NULL, [Wage] [Money] NOT NULL, [Month Number] [Char] (5) NULL, [Level] [INT] NULL) AS Begin Declare @LEVEL AS INT SELECT @Level = 0 INSERT INTO @treeinfo SELECT [employee number], [Name], [Age], [Payroll], [Master 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. [Payroll], E. [Master Number], @LEVEL FROM [Employee Information] As e join @treeinfo as t on E. [上 号] = T. [Employee number] and T. [Level] = @LEVEL - 1 End Return End The following is the result of the test: SELECT * from dbo.getsubtreeInfo 'E9903') employee number names age salary upper level number level -------- --------------- - E9903 Zheng Ke 38 5000.0000 E9901 0 E9906 Xiao Ying 26 3350.0000 E9903 1 E9907 Huang Jingjing 22 2800.0000 E9906 2 Finally, let's look at an interesting example.
