The processing of tree-type structure

The tree structure is widely used in the application, and it needs some skills to deal with the data operation:

There are a number of ways to define a table structure.
. Data table:
A. One-way linked table structure (this is the name given to it, because it is similar to the structure of the list, in addition to the necessary description of itself, only the location of the parent node)
DDL Example:
--equivalent one-way linked list
CREATE TABLE Linktree (
ID varchar (TEN) primary key,--node ID
Nodecontent varchar (50),--node content
ParentID varchar Default '--parent Node ID
)

Advantages：
. The column width is fixed, the table structure is easy to maintain;
. Find the upstream/downstream node of the specified node is highly efficient;
Maintenance of tree-type structure is convenient (add/delete layer);
Disadvantage, a multiple select is required to complete the following:
List all subtrees starting at the specified node (except for Oracle support);
. Returns the path from the specified node A to its child node B (the difference between B and A is greater than 1);

DML Example:
Look down and Count child nodes (only one layer)
(1) Through the parent node ID through the parent node's ID
--Find child nodes
Select
*
From
Linktree
where
ParentID = ' 01 '
--count the number of child nodes
Select
ParentID,
COUNT (*) ' count '
From
Linktree
where
ParentID = ' 01 '
GROUP BY
ParentID

(2) There is a uniqueness constraint on the Content Condition column nodecontent of the parent node
--Find child nodes
Select
*
From
Linktree
where
ParentID = (select id from linktree where nodecontent = ' Root ')
--count the number of child nodes
Select
ParentID,
COUNT (*) ' count '
From
Linktree
where
ParentID = (select id from linktree where nodecontent = ' Root ')
GROUP BY
ParentID

Because the conditional column has no uniqueness constraints, it is possible to return a recordset when looking, and need to do some processing: conditional column nodecontent without uniqueness constraints
--Find child nodes, where only the parent row with child nodes is returned, and if you need to return all the right join
Select
Sub.id ParentID,
Main.id,
Main.nodecontent
From
Linktree Main
Join (SELECT ID from linktree where nodecontent = ' depart1 ') sub on main.parentid = Sub.id
ORDER BY
ParentID
--count the number of child nodes and use right join to return all statistics
Select
ParentID,
COUNT (ID) ' Count '
From (
Select
Sub.id ParentID,
Main.id
From
Linktree Main
Right join (SELECT ID from linktree where nodecontent = ' depart1 ') sub on main.parentid = Sub.id
) tree
GROUP BY
ParentID

(3) Find sibling node lookup sibling node
--Using a unique column
Select
ParentID,
ID Brotherid,
Nodecontent
From
Linktree
where
ParentID = (select ParentID from Linktree where id= ' 01 ')
--Using a Non unique column, due to a parent-child response, you need to be aware of using the DISTINCT keyword
Select
Sub.parentid,
Main.id Brotherid,
Main.nodecontent
From
Linktree Main
Join (SELECT distinct parentid from linktree where nodecontent = ' depart1 ') sub on main.parentid = Sub.parentid
ORDER BY
ParentID

(4) traversing the tree
Because of the characteristics of the linked table structure, it is decided that it is impossible to complete the operation with a single Select, and different strategies can be adopted for different DBMS:
such as Oracle
SELECT * from Linktree start and id= ' connect by PRIOR id = parentid;
such as ms-sql2000
The structure of the table determines that the data can only be searched on a level-by-layer basis, and it is common to use stored procedures for recursive return table variables.
Here I use the loop to achieve, more efficient than recursive + table variables. ms-sql2000 Traversal Tree
--The two parameters defined here
Declare
@level int,--Lookup hierarchy
@start varchar (2); --Start Node ID
Select @level = 3, @start = ' 00 '; --Sample argument
--Temporary variables
declare @flag int; --level of current processing node
Declare
@t table (
ID varchar (2),
Nodecontent varchar (20),
ParentID varchar (2),
Site varchar (200),
Flag int
);
--Initialization
Set @flag = 1;
INSERT INTO @t
Select
Linktree.*,
Linktree.id,
@flag
From
Linktree
where
id = @start
--loop lookup, End condition (1. Reach the specified lookup level; 2. There are no matching records;)
While @ @rowcount > 0 and @flag < @level
Begin
Set @flag = @flag