In the writing of BBS, some people often ask how to achieve a tree structure? A more irresponsible answer is: use the recursive algorithm. Of course, recursive is a feasible approach (the bifurcation of the binary tree can only use the recursive algorithm), but for BBS, this is necessary to carry out a lot of SQL query (although you can use the stored procedure, but At all speeds up, the faster algorithm should be considered). A viable algorithm for achieving a tree structure is given below.
The Next, another tree structure is implemented using the "Using Medium Value Sort Base Method": First, Main Thoughts: Increase a sort base number ORDERNUM, reply to insert a post in the same sticker, ordering the base ORDERNUM Take the median of both. In order to narrow, only fields related to the tree structure are discussed here.
Add three redundant fields in the table, rootid - is used to record the root ID, DEEP - is used to record the depth of the reply (0 indicates the root sticker), the orderNum - sorting the base (the key).
Table forum and (only fields related to the tree structure): ID rootid DeepOrdernum Among them, rootid, Deep is int type (Deep can be Tinyint), ordernum is Float type.
Example: (In order to simply, use a small start sort base, in practical applications, the larger starting group should be used, and the integer power of 2 should be used, such as 65536 = 2 ^ 16, said below Sorting means that according to ORdernum from a small to large. ID rootiddeepOrdernum 100 0 211 64 ____________________________ 31 1 32 Reply to the first post, take the median value of 1,2 base (0 64) / 2
The result of sorting is: ID rootiddeepOrdernum 10 0 0 311 32 2 1 1 64 _____________________________ 41 2 48 Reply to the third post, taking 3, 2 median (32 64) /2
The result of sorting is: ID rootiddeepOrdernum 10 00 311 32 4 12 48 211 64 ______________________________ 513 56 Reply to the 4th Post, the median value of 4, 2 is (48 64) / 2
The result is: ID rootiddeepOrdernum 10 0 0 311 32 4 12 48 513 56 211 64 _____________________________ 6 12 40 Reply to the third post, the result of 3, 4, namely (32 48) / 2, the result is: ID rootiddeepordernum 1 00 0 311 32 612 40 4 12 48 513 56 211 64
Such sorting courage ORDERNUMs achieve the following tree structure with the reply depth Deep: ID 1 3 6 4 5 2
Second, the implementation of insertion (how to determine the sort base, the sub-stickers referred to below) (1) Root ORderNum is set to 0 (2) First Reply Post Base Demand is 2 integers (Such as 65536 = 2 ^ 16, more than a large number) (3) When the last post is replying, the base number takes the final number ORDERNUM plus 2 integer power (同) (4) Reply to the middle When the child, the base ORDERNUM is mediated in the base of the previous post.
Third, the deleted implementation When deleting a post (twig), simply find the next reply depth DEEP less than or equal to the reply depth (deEep) to delete the label, then the base ORDERNUM is located in two stickers The post between the sub-coupling is removed. As the above example, the subjjicles are deleted under 3 post (base 32). Since the depth of 3 is 1, the next depth is less than or equal to 1 post (its base is 64), then only The post required to delete the base in 32 to 64 (except 64). That is to delete 3, 6, 4, 5 posts. To delete others.
Fourth, the display is displayed Simply executes SELECT * FROM FORUM ORDER BY ROOTID ID-SIGN (ROOTID) * ID DESC, ORDERNUM, and then combines DEEP to realize the display.
5. Specific implementation methods (as a memory process as an example)
Additional stored procedure: (omitted registered user detection and integral part of the content)
Create Procedure [Add] @keyid int, @MESSAGE VARCHAR (50) OUTPUT --- Keyid is a reply post ID number, if it is a new post, @ 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, @ Deep int, @ Begnum float , @ EndNum float, @ otnum float SELECT @ rootid = 0, @ id = 0, @ Deep = 0, @ begnum = 0, @ Endnum = 0, @ otnum = 0 Select @ rootid = rootid, @ ID = ID, @ Begnum = ORDERNUM, @ Deep = Deep from Forum where id = @ Keyid IF (@ id = 0) Begin @Select @ Message = 'The post to reply has been deleted! 'return END Else Begin (@ rootid = 0) Select @ rootid = @ ID - Reply is the root sticker, take the ID as a new package Rootid SELECT @ EndNum = Ordernum where rootid = @ rootid and ordernum> @begnum order by ordernum IF (@ Endnum = 0) select @ otnum = @ Begnum 65536 - - Reply is the last sticker = (@ Begnum @ Endnum) / 2 - the key, taking the sorting median medinus insert Into forum (rootid, deep , ORDERNUM, ...) VALUES (@ rootid, @ Deep 1, @ otnenum, ...) End END SELECT @ Message = 'Success' return
Scheduled stored procedure: (omitted registered user detection and integral part content) Create Procedure [DEL] @KeyId Int, @ Message Varchar (50) Output --- KeyId is the post ID number to be deleted, if it is a new post 0, @ Message is an error message as declare @rootid int, @ ID int, @ Deep int, @ begnum float, @ Endnum float select @ rootid = 0, @ Deep = 0, @ begnum = 0, @ Endnum = 0, @ id = 0 SELECT @ id = id, @ begnum = OrderNum, @ rootid = rootid, @ Deep = Deep from forum where id = @ Keyid IF (@ id = 0) begin SELECT @ Message = 'This post is not exist! " RETURN ELSE BEGIN SELECT @ Endnum = ORDERNUM from Forum where rootid = @ rootid and deep <= @ Deep and ORDERNUM> @Begnum ORDER BY ORDERNUM IF (@ Endnum = 0) - To delete Is the last child, delete from forum where ordernum> = @ begnum and (rootid = @ rootid or id = @ rootid) else delete from forum where ordernum> = @ begnum and ordernum <@endnum and (rootid = @rootid or id = @ rootid) END Displays the stored procedure (omitted)
Summary: Since the childnum field is omitted, if you want to know how many sub-stickers (or sub-stickers), you need to use a statistical method or increase the corresponding field record, which may not be listed as a tree structure discussion.
