BBS tree structure display can have many kinds of methods, which are more easily thought of is recursive and sort string method, but these two methods are not very good, then what is a more reasonable algorithm?
Recursive methods do not need to say, we all know how to use, first talk about the sorting string method, the simplest sort string method can be used, only one ID can complete the tree, to this
1 001
2 002
3 001001
4 001001001
5 001002001
This is done by sorting this string:
001
001001
001001001
001002001
002
This method is easy to implement, but the disadvantage is also very obvious, one is the number of replies is limited, and the other with the increase will become longer, affecting the efficiency of the database.
The following method is Li long and is a flexible sort string method
Ddl
--------------
CREATE TABLE dbo. Message
(
ID Numeric (18,0) IDENTITY (1000,1),
DateAndTime datetime DEFAULT getdate () not NULL,
Authorid Numeric (18,0) not NULL,
Subject nvarchar () not NULL,
Body ntext NULL,
Linkurl nvarchar (MB) NULL,
Textforlink nvarchar (m) NULL,
ImageURL nvarchar (MB) NULL,
Class int DEFAULT 0 not NULL,
Clientinfo nvarchar () NULL,
remoteaddr nvarchar (m) NULL,
CONSTRAINT Pk_bbsmessage
PRIMARY KEY Nonclustered (Id,authorid)
)
Go
CREATE TABLE dbo. Msgreftab
(
MsgID Numeric (18,0) not NULL,
ParentID Numeric (18,0) not NULL,
Ancestorid Numeric (18,0) not NULL,
Childnum Numeric (18,0) DEFAULT 0 not NULL,
Linkstr nvarchar () not NULL,
CONSTRAINT Pk_bbsreftab
PRIMARY KEY Nonclustered (MsgID)
)
Go
-----------------
Stored procedures:
-----------------
--Draw
CREATE PROCEDURE Sp_summary
@HaveBody bit,
@from Numeric,
@to Numeric
As
IF (@HaveBody = 1)
Select T.id,t.dateandtime,m.nickname AS
Author,m.email,t.subject,t.body,t.linkurl,t.textforlink,t.imageurl,s.childnu
M,s.parentid
From Message t
, Msgreftab as S
, (SELECT MsgID from msgreftab WHERE parentid = 0) as F
, members as M
where T.id=s.msgid
and F.msgid = S.ancestorid
and f.msgid between @from and @to
and M.memberid = T.authorid
ORDER BY S.ancestorid,s.linkstr
ELSE
Select T.id,t.dateandtime,m.nickname AS
Author,m.email,t.subject,t.linkurl,t.textforlink,t.imageurl,s.childnum,s.par
Entid
From Message t
, Msgreftab as S
, (SELECT MsgID from msgreftab WHERE parentid = 0) as F
, members as M
where T.id=s.msgid
and F.msgid = S.ancestorid
and f.msgid between @from and @to
and M.memberid = T.authorid
ORDER BY S.ancestorid,s.linkstr
Go
--Add stickers
CREATE PROCEDURE Sp_add_message
@AuthorID Numeric,
@Subject nvarchar (250),
@Body ntext,
@LinkURL nvarchar (100),
@TextForLink nvarchar (50),
@ImageURL nvarchar (100),
@ParentID Numeric,
@ID Numeric OUTPUT,
@ChildNum Numeric OUTPUT,
@LinkStr nvarchar () OUTPUT,
@AncestorID Numeric OUTPUT
As
INSERT into message (
Authorid,
Subject,
Body,
Linkurl,
Textforlink,
ImageURL)
VALUES (
@AuthorID,
@Subject,
@Body,
@LinkURL,
@TextForLink,
@ImageURL)
SELECT @ID = @ @IDENTITY
UPDATE Msgreftab
SET
Childnum = childnum+1
WHERE
MsgID = @ParentID
SELECT @ChildNum = Childnum,
@LinkStr = Linkstr,
@AncestorID =
