Tree type Structure
Implement tree structure (part One)
Author: ACE Last updated: 06/08/2000 Category: Original
Take a look at the example, and if you feel right, continue reading this http://www.coolbel.com/ace/articles/test/msdn.asp.
1. Briefly
For everyone, the tree structure is a familiar model. Its application is very extensive, such as organizational structure, BOM, data file management, asset management and so on are based on tree-type structure. In real life, there are many things that can be abstracted into a tree structure. This structure simplifies understanding of certain things and makes the concept clear.
2. Table Structure
The table structure of a tree structure can be simple or complex. According to different requirements, the table structure is not immutable, the method of reading data is also different.
Let's consider one of the simplest situations, looking at the following example:
Sample Table
Child node Parent node Title article
1 0 Program 0
2 1 Visual Basic 0
3 1 Power Builder 0
4 1 C + + Builder 0
5 2 ADO Control 1
..........
A more intuitive representation:
Program
|-------Visual Basic
| |------------ADO Control
|-------Power Builder
|-------C + + Builder
This structure is very simple, when you modify the relationship between the elements, you only need to modify the parent node can be, such as the ADO control as a child of program, as long as the ADO control of the corresponding Parent node to 1. From this, it is not difficult to see that this structure is simple and easy to use.
3. Reading data using stored procedures
How to retrieve the data, perhaps we are most concerned about. For the user, the expression of the data to be easy to understand. From the example above, it can be seen that intuitive representations are easier to understand than displaying the data storage structure.
The key is how to get the hierarchical relationship between the elements, with a hierarchical relationship, you can get like a resource manager like the interface.
Here, we use the stored procedure to complete this function. In Microsoft's numerous SQL related documents, there is a section of SQL code is very classic, we have the spirit of copycat, we modify it, foreign serve China.
CREATE PROC sp_listfile (@Child_node int)
As
SET NOCOUNT on
--declare var
DECLARE @lvl smallint--Hierarchical relationship
DECLARE @c_ID int
DECLARE @article Bit--whether it is a sign of the article
DECLARE @title varchar (150)--title
--create temporary table
CREATE TABLE #stack (child_node int,lvl smallint)
--create target Table
CREATE TABLE #FileList
(LVL smallint,
child_node_id int,
Article bit,
Title varchar (150))
--initial
INSERT into #stack VALUES (@Child_node, 0)
SELECT @Lvl = 0
--main Loop
While @Lvl >-1
BEGIN
IF EXISTS (SELECT * from #stack WHERE LVL = @Lvl)
BEGIN
SELECT @Child_node = Child_node
From #stack
WHERE LVL = @Lvl
SELECT @article = article, @title = title
From some_table
WHERE Child_node = @Child_node
INSERT into #FileList
VALUES (@Lvl, @Child_node, @article, @title)
DELETE from #stack
WHERE LVL = @Lvl and Child_node = @Child_node
INSERT into #stack
&nb
