SQL Server recursive query implementation method

Take table Deparment as an example

Table structure:

Id department internal code,

DeptCode department code,

ParentDeptId internal code of the superior department

Use T-SQL:

The code is as follows:	Copy code
With Dep ( Select Id, DeptCode, DeptName from Department where Id = 1 Union all Select d. Id, d. DeptCode, d. DeptName from Dep Inner join Department d on dep. Id = d. ParentDeptId )

Select * from Dep: with as is a keyword of the CTE (Common Table Expression) new feature of SQL server, used to store temporary result sets. It is often used to replace subqueries. In this example, we can understand that, after finding the record with Id = 1, it is stored in the temporary table Dept, and then the temporary table and Department are connected internally to find its subrecords. After the sub-records are combined with the first record, they are used as the new result set of Dept to continue the inner join and find new sub-records.

Use PL/SQL:

The code is as follows:	Copy code
Select Id, DeptCode, DeptName From Department Start with Id = 1 Connect by prior Id = ParentDeptId;

Start with indicates which record to start recursive query, that is, the root node
Connect by indicates recursion, followed by recursion conditions
Prior indicates the previous record, indicating the Id of the previous record = ParentDeptId of the next record
For example, the preceding SQL statement can be understood as, taking the record with Id 1 as the root node, recursively querying the ParentDeptId of the next record = the Id of the previous record

Instance

The code is as follows:	Copy code
Is the department name superior ID? 1 y Department 0 1 31 department y 1 1 1 32 n Zhang San 31 33 n Li 2 31 34 y Department 2 31 35 n Wang Wu 34 35 y Department 3 34 36 n small 3 35

I want to contact all persons with the ID of 35 lower-level personnel, including all persons in lower-level departments.
 

The code is as follows:

Copy code

-- Create a query function Create function f_id ( @ Id int -- id to be queried ) Returns @ re table (id int, level int) As Begin Declare @ l int Set @ l = 0 Insert @ re select id, @ l From table Where parent id = @ id While @ rowcount> 0 Begin Set @ l = @ l + 1 Insert @ re select a. id, @ l From table a join @ re B on a. Parent id = B. id and B. level = @ L-1 End Return End Go -- Call a function for query Select a. * from table a join f_id (35) B on a. id = B. id

-- Test data

The code is as follows:

Copy code

Create table (ID int, whether it is department char (1), Department name varchar (10), parent ID int) Insert table select 1, 'y', 'Department 0', 1 Union all select 31, 'y', 'Department 1', 1 Union all select 32, 'n', 'Zhang San', 31 Union all select 33, 'n', 'Lee 2', 31 Union all select 34, 'y', 'Department 2', 31 Union all select 35, 'n', 'Wang Wu', 34 Union all select 35, 'y', 'Department 3', 34 Union all select 36, 'n', 'small three', 35 Go -- Create a query function Create function f_id ( @ Id int -- id to be queried ) Returns @ re table (id int, level int) As Begin Declare @ l int Set @ l = 0 Insert @ re select id, @ l From table Where parent id = @ id While @ rowcount> 0 Begin Set @ l = @ l + 1 Insert @ re select a. id, @ l From table a join @ re B on a. Parent id = B. id and B. level = @ L-1 End Return End Go -- Call a function for query Select a. * from table a join f_id (35) B on a. id = B. id Go -- Delete test Drop table Drop function f_id /* -- Test result Is the department name superior ID? ------------------------------------- 36 n small 3 35 (The number of affected rows is 1) --*/