To implement a tree structure using the median ordering cardinality method (i)

Sort | Sort in the compilation of BBS, people often ask how to achieve tree structure? A more irresponsible answer is to use a recursive algorithm. Of course, recursion is a viable option (a binary tree can be used as a recursive algorithm only), but for BBS, this is bound to do a lot of SQL queries (although you can use stored procedures to do, but to fundamentally speed up, you should consider the faster algorithm).
The following is a feasible algorithm for the implementation of a tree-like structure with complete screen-discard.

 here is another way to implement a tree structure using the median sort cardinality method:
First, the main idea: Add a sort base digital segment ordernum, reply to the same root post inserted posts, sorted cardinal Ordernum take both of the median value.
 for the brevity of the narrative, only the fields related to the tree structure are discussed here.

Add three redundant fields to the table, rootid--is used to record the depth that the root id,deep--is used to record the reply (the root sticker when 0), and the ordernum--sort cardinality (the key).

Table Forum with (only columns related to tree structure):
Idrootiddeepordernum
Where the ID, Rootid, deep are int (deep can be tinyint type), ordernum for float type.

For example: (in order to be simple, using a small starting sort cardinality, in practical applications, should use a large starting base, and should take 2 of the integer power, such as 65536=2^16, the following said sort refers to ordernum from small to large sort).
Idrootiddeepordernum
1000
21164
______________________________
31132 reply to the 1th post, take 1, 2 cardinal value that is (0+64)/2

After sorting, the result is:
Idrootiddeepordernum
1000
31132
21164
______________________________
41248 reply to the 3rd post, take 3, 2 base value that is (32+64)/2

After sorting, the result is:
Idrootiddeepordernum
1000
31132
41248
21164
______________________________
51356 reply to the 4th post, take 4, 2 base value that is (48+64)/2

The result of sorting is:
Idrootiddeepordernum
1000
31132
41248
51356
21164
______________________________
61240 reply to the 3rd post, take 3, 4 base value that is (32+48)/2

The result of sorting is:
Idrootiddeepordernum
1000
31132
61240
41248
51356
21164

This sort cardinality ordernum with the reply depth deep to achieve the following tree structure:
Id
1
3
6
4
5
2


Second, the insertion of the implementation (how to determine the sorting base, the following refers to the post are the same root of the child)
(a) root ordernum set at 0
(b) The number of the first reply posts is set to 2 of the integer power (e.g. 65536=2^16, preferable number)
(iii) When replying to the last post, the radix ordernum the cardinal number of the last paste plus 2 of the integer Power (IBID.)
(iv) Back to the middle of the post, Cardinal Ordernum before and after the base value of the posts

III. implementation of deletion
 Delete post (pruning), just find the next reply depth deep less than or equal to delete the reply depth (deep) posts, and then the base ordernum located in two posts between the base of the deletion can be achieved pruning.
 in the example above, to remove a 3-post (base 32) of the branch, because 3 of the depth of 1, the next depth is less than or equal to 1 post is 2 paste (its base is 64), then simply delete the base in 32 to 64 (excluding 64) posts on the line. That is to delete the 3, 6, 4, 5 paste. To delete the other is likewise.

Iv. implementation of the display
 only needs to perform a SELECT * from Forum ORDER by Rootid+id-sign (Rootid) *id
Desc,ordernum, and then combine deep to achieve a tree-like display.


V. Specific implementation methods (in the case of stored procedures)

Add and paste stored procedures: (Omit registered user detection and integral part of the content)

CREATE PROCEDURE [Add] @keyid int, @message varchar (50)
Output ——— keyID is the post ID number for the reply, if the new sticker is 0, @message is an error message
As
if (@keyid =0)
insert into forum (rootid,deep,ordernum,......) VALUES (0,0,0,......)
else
begin
declare @rootid int, @id int, @deep int, @begnum float, @endnum
float, @ordernum float
select @rootid =0, @id =0, @deep =0, @begnum =0, @endnum =0, @ordernum =0
select @rootid =rootid, @id =id, @begnum =ordernum, @deep =deep from
Forum where id= @keyid
if (@id =0)
begin
select @message = ' The posts to reply have been deleted! '
return
end
else
begin
if (@rootid =0) SELECT @rootid = @id --reply is a root paste, with its ID of the new sticker Rootid
select @endnum =ordernum where rootid= @rootid and
Ordernum> @begnum ORDER BY Ordernum
if (@endnum =0)
select @ordernum = @begnum +65536--reply is the Last Post
else
select @ordernum = (@begnum + @endnum)/2--key, taking values in sorted cardinality
insert into forum (rootid,deep,ordernum,......)
VALUES (@rootid, @deep +1, @ordernum,......)
end
end
select @message = ' success '
return