Summary of tree table data processing in SQL Server

-- Use the function method:

-- Create a demo Environment

If object_id ('tb _ bookinfo') is not null drop table tb_bookinfo
Go
Create Table tb_bookinfo (number int, name varchar (10), type INT)
Insert tb_bookinfo
Select 1, 'n1 ', 6 Union all
Select 2, 'n2 ', 3

If object_id ('tb _ booktype') is not null drop table tb_booktype
Go
Create Table tb_booktype (ID int, typename varchar (10), parentid INT)
Insert tb_booktype
Select 1, 'English ', 0 Union all
Select 2, 'bio', 0 Union all
Select 3, 'computer ', 0 Union all
Select 4, 'colloquial ', 1 Union all
Select 5, 'audience', 1 Union all
Select 6, 'database', 3 Union all
Select 7, 'Software Project', 3 Union all
Select 8, 'SQL Server', 6

Select a. *, B. Level from tb_bookinfo A, f_getc (3) B where a. type = B. ID order by B. Level
/*
Number name Type level
-------------------------------------------
2 N2 3 0
1 N1 6 1

(The number of affected rows is 2)
*/
-- Query all parent nodes
If object_id ('f _ getp ') is not null drop function f_getp
Go
Create Function f_getp (@ id int)
Returns @ Re table (ID int, level INT)
As
Begin
Declare @ l int
Set @ l = 0
Insert @ Re select @ ID, @ l
While @ rowcount> 0
Begin
Set @ l = @ L + 1
Insert @ Re select a. parentid, @ l from tb_booktype A, @ Re B
Where a. ID = B. ID and B. Level = @ L-1 and A. parentid <> 0
End
Update @ Re set level = @ l-level
Return
End
Go

-- Query all subnodes
If object_id ('f _ getc') is not null drop function f_getc
Go
Create Function f_getc (@ id int)
Returns @ Re table (ID int, level INT)
As
Begin
Declare @ l int
Set @ l = 0
Insert @ Re select @ ID, @ l
While @ rowcount> 0
Begin
Set @ l = @ L + 1
Insert @ Re select a. ID, @ l from tb_booktype as A, @ Re as B
Where B. ID = A. parentid and B. Level = @ L-1
End
Return
End
Go

-- Query all Parent and Child Nodes
If object_id ('f _ getall') is not null drop function f_getall
Go
Create Function f_getall (@ id int)
Returns @ Re table (ID int, level INT)
As
Begin
Declare @ l int
Set @ l = 0
Insert @ Re select @ ID, @ l
While @ rowcount> 0
Begin
Set @ l = @ L + 1
Insert @ Re select a. parentid, @ l from tb_booktype A, @ Re B
Where a. ID = B. ID and B. Level = @ L-1 and A. parentid <> 0
End
Update @ Re set level = @ l-level
While @ rowcount> 0
Begin
Set @ l = @ L + 1
Insert @ Re select a. ID, @ l from tb_booktype as A, @ Re as B
Where B. ID = A. parentid and B. Level = @ L-1
End
Return
End
Go
 

-- Delete demo

Drop table tb_bookinfo

Drop table tb_booktype

Drop function f_getp

Drop function f_getc
Drop function f_getall
Go

 

 

 

-- New sqlserver2005 Method

-- Create a demo Environment
If object_id ('[dept]') is not null
Drop table [dept]
Go
Create Table dept (
Id int primary key,
Parent_id int,
Name nvarchar (20 ))
Insert Dept
Select 1, 0, N 'finance author' Union all
Select 2, 0, N 'administrative authorization' Union all
Select 3, 0, N 'business' Union all
Select 4, 0, N 'business' Union all
Select 5, 4, N 'salesman' Union all
Select 6, 4, N 'mis 'Union all
Select 7, 6, N 'ui' Union all
Select 8, 6, N 'softwar' Union all
Select 9, 8, N 'internal developing'
Go
-- 1. Parent-> child
-- Query all departments under a specified department
Declare @ dept_name nvarchar (20)
Set @ dept_name = n' MIS'
;
Depts (
-- Positioning Point member
Select * from Dept where name = @ dept_name
Union all
-- Recursive Member, recursively implemented by referencing the CTE itself and joining the dept base table
Select a. * From dept a, depts B where a. parent_id = B. ID
)
Select * From depts
Go
-- The result is as follows:
/*
Id parent_id name
------------------------------------------
6 4 MIS
7 6 UI
8 6 Software Development
9 8 Internal Development

(The number of affected rows is 4)
*/

-- 2. Child-> parent
-- Query all departments under a specified department
Declare @ dept_name nvarchar (20)
Set @ dept_name = n' internal developers'
;
Depts (
-- Positioning Point member
Select * from Dept where name = @ dept_name
-- Select D. id, D. parent_id, D. Name, convert (nvarchar (50), D. Name) as parent from Dept where @ dept_name
Union all
-- Recursive Member, recursively implemented by referencing the CTE itself and joining the dept base table
Select a. * From dept a, depts B where a. ID = B. parent_id
)
Select * From depts
Go

-- The result is as follows:
/*
Id parent_id name
------------------------------------------
9 8 Internal Development
8 6 Software Development
6 4 MIS
4 0 business department

(The number of affected rows is 4)
*/

-- Delete the demo Environment
Drop table Dept

 

 

 

A good example

If object_id ('[TB]') is not null drop table [TB]
Go
Create Table [TB] ([modeid] int, modename varchar (20), parentid INT)
Insert [TB]
Select 100, 'item management', 0 Union all
Select 101, 'order management', 0 Union all
Select 102, 'user management', 0 Union all
Select 104, 'school ads', 0 Union all
Select 105, 'System settings', 0 Union all
Select 106, 'attachment management', 0 Union all
Select 107, 'item management', 100 Union all
Select 108, 'detail management', 100 Union all
Select 109, 'logistics management', 100 Union all
Select 110, 'item information management', 107 Union all
Select 111, 'item category management', 107 Union all
Select 112, 'recycle bin management', 107 Union all
Select 114, 'Group buying management', 108 Union all
Select 115, 'auction management', 108 Union all
Select 116, 'discount management', 108 Union all
Select 117, 'member management', 102 Union all
Select 118, 'Membership card management', 102 Union all
Select 119, 'Fund management', 102 Union all
Select 120, 'administrator management', 102 Union all
Select 121, 'add admin', 120 Union all
Select 122, 'modify admin', 120
Go

-- Query all subnodes
If object_id ('f _ getc') is not null drop function f_getc
Go
Create Function f_getc (@ id int)
Returns @ Re table (ID int, level int, sort varchar (10 ))
As
Begin
Declare @ l int
Set @ l = 0
Insert @ Re select @ ID, @ l, null
While @ rowcount> 0
Begin
Set @ l = @ L + 1
Insert @ Re select a. modeid, @ l, ltrim (isnull (B. Sort, A. modeid) from TB as A, @ Re as B
Where B. ID = A. parentid and B. Level = @ L-1
End
Update @ Re set level = level-1
Return
End
Go

Select. modeid,. parentid, replicate ('', B. level) + 'authorization' +. modename, B. level, B. sort from tb a, f_getc (0) B
Where a. modeid = B. ID
Order by case when B. level <2 then 0 else 1 end, B. Sort, B. Level

/*
Modeid parentid sort level
---------------------------------------------------------------------------------------------
100 0 items Commodity Management 100 0
107 100 Commodity Management 100 1
108 100 Details Management 100 1
109 100 Logistics Management 100 1
101 0 orders management 101 0
102 0 users user management 102 0
117 102 Member Management 102 1
118 102 member card management 102 1
119 102 RMB capital management 102 1
120 102 administrator management 102 1
104 0 school ads 104 0
105 0 worker system settings 105 0
106 0 attachment management 106 0
110 107 commodity information management 100 2
111 107 Classification Management of commodities 100 2
112 107 recycle bin Management 100 2
114 108 group buying Management 100 2
115 108 auction management 100 2
116 108 renewal discount Management 100 2
121 120 add administrator 102 2
122 120 modify administrator 102 2

(The number of affected rows is 21)

*/

 

-- Query all child nodes with paths and sorting
If object_id ('f _ getc') is not null drop function f_getc
Go
Create Function f_getc (@ id int)
Returns @ Re table (ID int, level int, sort varchar (100), path varchar (500 ))
As
Begin
Declare @ l int
Set @ l = 0
Insert @ Re
Select [modeid], @ l, right ('000000' + ltrim (modeid), 5), modename
From TB where parentid = @ ID
While @ rowcount> 0
Begin
Set @ l = @ L + 1
Insert @ Re
Select a. modeid, @ L, B. Sort + right ('000000' + ltrim (A. modeid), 5 ),
B. Path + '-' + A. modename
From TB as A, @ Re as B
Where B. ID = A. parentid and B. Level = @ L-1
End
Update @ Re set level = level
Return
End
Go

Select. modeid,. parentid, replicate ('', B. level) + 'authorization' +. modename, B. level, B. sort, B. path from tb a, f_getc (0) B
Where a. modeid = B. ID
Order by sort

/*
Modeid parentid level
-----------------------------------------------------------------------------------------------------------------
100 0 items product management 0 00100 Items Management
107 100 Commodity Management 1 0010000107 commodity management-Commodity Management
110 107 commodity information management 2 001000010700110 commodity management-commodity information management
111 107 Classification Management of commodities 2 001000010700111 product management-Product Classification Management
112 107 recycle bin Management 2 001000010700112 product management-Recycle Bin Management
108 100 Details Management 1 0010000108 product management-Details Management
114 108 renewal group buying Management 2 001000010800114 product management-Detail management-group buying Management
115 108 million auction management 2 001000010800115 goods management-Detail management-auction management
116 108 renewal discount Management 2 001000010800116 product management-Detail management-discount Management
109 100 Logistics Management 1 0010000109 commodity management-Logistics Management
101 0 orders management 0 00101 orders management
102 0 Users Management 0 00102 Users Management
117 102 Member Management 1 0010200117 user management-member management
118 102 member card management 1 0010200118 user management-member card management
119 102 RMB fund management 1 0010200119 user management-Fund Management
120 102 administrator management 1 0010200120 user management-administrator management
121 120 add administrator 2 001020012000121 user management-administrator management-add Administrator
122 120 modify administrator 2 001020012000122 user management-administrator management-Modify Administrator
104 0 school ads 0 00104 school ads
105 0 RMB system settings 0 00105 system settings
106 0 attachment management 0 00106 attachment management

(21 rows are affected)

*/

 

 

----------

-- Delete a node

-- Create a test environment
If object_id ('goodtype') is not null drop table goodtype
Go
Create Table goodtype
(
Id int,
Name varchar (20 ),
PID int,
Tree int
)
Go

Insert goodtype
Select '1', 'A', '0', '0'
Union all select '2', 'A', '1', '1'
Union all select '3', 'AB', '1', '1'
Union all select '4', 'aaa', '2', '2'
Union all select '5', 'aba ', '3', '2'
Union all select '6', 'aba', '5', '3'
Union all select '7', 'abab', '5', '3'
Go
Create trigger trd_goodtype on goodtype instead of Delete
As
Begin
Update t set T. PID = D. PID, T. Tree = T. Tree-1
From goodtype T, deleted d
Where T. PID = D. id
Delete goodtype where ID in (select ID from deleted)
End
Go
Create trigger tru_goodtype on goodtype for update
As
Begin
Update t set T. Tree = T. Tree-1
From goodtype T, inserted I, deleted d
Where T. PID = I. ID and T. PID = D. id and I. Tree = D. Tree-1
End
Go
-- Query
Delete goodtype where id = 3
Select * From goodtype
Go
-- Result
/*

(7 rows affected)

(2 rows affected)

(One row is affected)

(One row is affected)

(One row is affected)
ID name PID tree
-----------------------------------------------------
1 A 0 0
2 aa 1 1
4 AAA 2 2
5 ABA 1 1
6 abaa 5 2
7 Abab 5 2

(6 rows affected)
*/