The supplement of sorting "median order cardinal method to achieve tree structure"
Due to a momentary negligence, the method "for the base digit segment of type int, there can only be 31 replies to the original post, the base number of the numeric type, and no more than 120 replies to the original paste (in fact, for the Int field, the original post reply to the 32nd tree structure shows the beginning of the disorder, For a numeric-type base digit segment, the original post response is from more than 121 tree-like structures showing the beginning of the disorder--the response is not problematic--due to computer storage accuracy.
We can modify the pasted stored procedure (add the line preceded by the * * number) to limit it to a certain depth (in a specific data type, cardinality cannot be distinguished) is no longer displayed in a tree structure (and is displayed in parallel), although somewhat like a compromise, in fact due to limitations of the browser, Even if the depth of 100 can be shown in a tree structure, but the result of the tree structure from your browser is still not clear-the screen width is not enough, will be folded line chant.
To add a stored procedure:
CREATE PROCEDURE [Add] @keyid int, @message varchar OUTPUT ——— keyID as the post ID number of the reply, if the new paste is 0, @message 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 to the root paste, take its ID as the new pasted Rootid
SELECT @endnum =ordernum where rootid= @rootid and ordernum> @begnum ORDER by Ordernum
IF (@endnum =0)
SELECT @ordernum = @begnum +65536--reply is the last one where you can limit the range of @ordernum to prevent overflow
ELSE
* * BEGIN
* * IF @endnum-@begnum >1--accuracy can still be distinguished. The 1 here is a precision tag, suitable for the base number segment is int, if the basis number segment is numeric field, please choose to Marry (Pooh Pooh, Typos Come), the purpose is to make the base precision over the hour limit depth increase, avoid the display when the disorder
* * SELECT @ordernum = (@begnum + @endnum)/2, @deep = @deep +1--key, take the sorted cardinality median
* * ELSE
* * SELECT @ordernum = @begnum-Limit depth can not be increased, this paste and reply to the paste parallel display, if there is a parentid field, you want to take the ParentID and reply to paste ParentID the same
* * End
* * INSERT into forum (rootid,deep,ordernum,......) VALUES (@rootid, @deep, @ordernum,......)
End
End
Select @message = ' success '
Return
The pruning stored procedure is changed to:
CREATE PROCEDURE [del] @keyid int, @message varchar OUTPUT ——— keyID for the post ID number to be deleted, if the new paste is 0, @message 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 does not exist! "
Return
End
ELSE
BEGIN
SELECT @endnum =ordernum from forum where rootid= @rootid and deep<= @deep and ordernum> @begnum ORDER by Ordernum
IF (@endnum =0)--the last branch or root post to be deleted
DELETE from forum where ordernum>= @begnum and (rootid= @rootid or id= @rootid)
ELSE
* * BEGIN
* * IF @begnum = @endnum
* * Delete from forum where id= @id and (rootid= @rootid or id= @rootid)-a branch that has been restricted by precision, delete only the current paste
* * ELSE
* * DELETE from forum where ordernum>= @begnum and ordernum< @endnum and (rootid= @rootid or id= @rootid)
* * End
End
Although it is limited, this limit should be necessary, because in fact, the depth of the reply is not too large (as I was here on the eight or nine floor, begging the cat shouted "Stop stop", hehe)
Welcome to my personal homepage http://swuse.yeah.net (originally Bigeagle is to say I this one sentence "The purpose is obvious")
