bbs的树形结构显示可以有很多种方法,其中比较容易想到的是递归和排序串方法,但这两种方法并不是很好,那么怎样才算是比较合理的算法呢?
递归方法不用讲,大家都知道怎么用,先讲讲排序串方法,最简单的排序串方法可以这样用,只用一个id就可以完成树型,向这样
1 001
2 002
3 001001
4 001001001
5 001002001
用这个字符串排序后就变成这样:
001
001001
001001001
001002001
002
这种方法容易实现,但缺点也是很明显,一个是回帖数受限制,另一个随着回帖增加会越来越长,影响数据库效率。
下面一种方法是李龙的,属于变通的排序串方法
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(250) NOT NULL,
Body ntext NULL,
LinkURL nvarchar(100) NULL,
TextForLink nvarchar(50) NULL,
ImageURL nvarchar(100) NULL,
Class int DEFAULT 0 NOT NULL,
ClientInfo nvarchar(250) NULL,
RemoteAddr nvarchar(50) 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(250) NOT NULL,
CONSTRAINT PK_BBSRefTab
PRIMARY KEY NONCLUSTERED (MsgID)
)
go
-----------------
存储过程:
-----------------
-- 抽出
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
-- 加贴
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(250) 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 = AncestorID
FROM MsgRefTab
WHERE
MsgID = @ParentID
go
---
是基于这样的想法:
贴子和跟贴都放在message表里,另有MsgRefTab对每一条信息都有描述。
父贴ParentID,0为不是子贴
祖宗贴AncestorID
直接跟贴数ChildNum
联接串LinkStr,学问都在这里,所有的跟贴都用一个数字字符串表示
如是
1011---> 为空
1012--->001 1011的跟贴,父贴LinkStr+父贴的子贴数+1
1013--->001001 1012的跟贴,父贴LinkStr+父贴的子贴数+1
1018--->001001001 1013的跟贴,父贴LinkStr+父贴的子贴数+1
1014--->001002 1012的跟贴,父贴LinkStr+父贴的子贴数+1
1017--->001002001 1014的跟贴,父贴LinkStr+父贴的子贴数+1
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=shu;UID=sa;PWD=;Database=books"
sql="select * from book "
Set rs=Server.CreateObject("ADODB.Recordset")
rs.cursorlocation=2
rs.open sql,conn,3,3
count=rs.recordcount
if rs.eof then
response.write("<br><center>啊呀,怎么会没有呢?奇怪,一本书也没有</center>")
Response.end
else
%>
<%
rs.PageSize=10
Page=CLng(Request("page"))
If Page<1 then Page=1
If page>rs.PageCount then Page=rs.PageCount
ShowOnePage rs,page
Next
Response.Write"</table></center>"
End Sub
Response.write"<div align=center><p>"
IF Page<>1 Then
Response.write"<a href=b_edit.asp?page=1><img border=0 src=/image/PAGE_1ST.GIF ></a> "
Response.write"<a href=b_edit.asp?page="&(Page-1)&"><img border=0 src=/image/PAGE_up.GIF ></a> "
End if
If Page<>rs.Pagecount then
Response.write"<a href=b_edit.asp?page="&(page+1)&">下一页</a>" Response.write"<a href=b_edit.asp?page="&rs.Pagecount&">上一页</a> "
End if
Response.write"</div>"
%>
<form action=b_edit.asp method=get>
<%
If Page="" then Response.write""
If Page<>1 Then
page=Request("page")
end if
end if
%>
<div align=right><HR>
共<font color="#800080"><%=rs.RecordCount%></font>记录
页码:<Font color=Red><%=Page%>/<%=rs.PageCount%></Font>
输入页码:<input type=text name=page size=3>
</div>
</form>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
declare @int_allid int
declare @int_beginid int,@int_endid int
declare @int_pagebegin int, @int_pageend int
select @int_allid=count(*) from tab_discuss where tint_level=0 and tint_tableid=@tint_tableid
select @int_recordcount=@int_allid --得出该版面的总贴数
declare cro_fastread cursor scroll
for select int_id from tab_discuss where tint_level=0 and tint_tableid=@tint_tableid order by int_id desc --这里定义游标操作,但是不用临时纪录集,而且游标也不需要全部遍历所有纪录集。
fetch absolute @int_beginid from cro_fastread into @int_pagebegin 将他的Id传给一个变量该页开始的Id
if @int_endid>@int_allid --这里要注意,如果某一页不足固定页数的纪录时。如只有一页纪录,而且纪录少于我们定义的数目。或者是最后一页时。。。
fetch last from cro_fastread into @int_pageend --直接将游标绝对定位到最后一条纪录,得出他的id号来。。。
else
fetch absolute @int_endid from cro_fastread into @int_pageend
select int_id,tint_level,tint_children,var_face,var_subject,datalength(txt_content) as int_len,sint_hits,var_url,var_image,var_user,dat_time,tint_tableid,bit_kernul from tab_discuss where tint_tableid=@tint_tableid and int_rootid between @int_pageend and @int_pagebegin order by int_rootid desc,num_order desc --我们就可以利用该页的第一个id和最后一个id得出中间的id来。。。。(注意。我们这个BBS的数性结构用了一种很巧妙的算法,就是用一个orderNum浮点数即可完成排序。。。)
--开始清场。。。
close cro_fastread
deallocate cro_fastread
return
我们再看看Asp页面里的程序操作。。。
pagenow=cint(request("pagenow")) --当前的页面。
if pagenow<=0 then pagenow=1
pagesize=10
set cmd=server.CreateObject("adodb.command")
cmd.ActiveConnection=strconn
cmd.CommandType=4
cmd.CommandText="pro_pageview"
set rs=cmd.Execute
if rs.eof then
Response.Write "目前已超过了纪录个数或纪录集为空!"
Response.End
end if
dim arrRs
arrRs=rs.getrows '可以利用getRows将纪录集迅速保存到一个二维的数组中来提高速度。
recordcount=cmd.Parameters("int_recordcount")
'注意,当纪录不足以刚好整除单位页纪录时,我们也要将其定义为一页,如纪录数目为2页多一个纪录,此时我们的页数也要为3页纪录。
if (recordcount mod pagesize)=0 then
pagecount=recordcount\pagesize
else
pagecount=recordcount\pagesize+1
end if
<div align="left" class="pblank" >
<%
'---------显示树性结构!-------------
level=0
Response.Write "<ul>"
for i=0 to ubound(arrRs,2)
if arrRs(1,i)>level then
Response.Write "<ul>"
end if
if arrRs(1,i)<level then
for j=arrRs(1,i) to level-1
Response.Write "</ul>"
next
end if
int_size=arrRs(5,i)
if int_size=0 then
str_size=" <无内容>"
else
str_size=""
end if
Response.Write "<li><img src=face\"&arrRs(3,i)&"><a href=showTitle.asp?int_id="&arrRs(0,i)&"&tint_tableid="&tint_tableid&" class=ptitle target=BoardAnnounce>"&server.HTMLEncode(arrRs(4,i))&"</a> "&str_size
if arrRs(7,i)<>"" then Response.Write " <连接> "
if arrRs(8,i)<>"" then Response.Write " <图像> "
Response.Write " -【"&arrRs(9,i)&"】 <font color=444444>"&arrRs(10,i)&"</font> [<font color=920092>ID:"&arrRs(0,i)&" 点击:"&arrRs(6,i)&"次</font>] <font color=444444>("&int_size&"字节)</font> <font color=ff0000>("&arrRs(2,i)&")</font></li>"