使用“使用中值排序基数法”实现树状结构(二)

网络整理 - 08-16

实现原理：以一排序字段（字符型实现排序），该字段的实际长度即为回复深度（用一位字符代表一层深度时）。
所受限制：回复深度只受排序串定义长度的限制（有点象空间换深度），每贴回复数（包括根贴和子贴）为30左右（当sql
server使用Dictionary order,case-insensitive排序方式，即不区分字母大小写时），如果sql server使用Binary orders排
序方式时受限为127（255？）。
改进方法：如果觉得不够用，可以使用多位字符对应一个深度（这样计算有点麻烦），或使用几位数字代表一个深度，例如3
位数字??最多可以999个子贴，不过些时排序字段的长度应为“3*最大深度”）
优点：此法是正则的??均匀的。

有关树状结构的字段：rootid、orderstr（varchar型，按需要深度定，假如你最大要使用20层回复深度，则定为varchar
(20)，余类推）

例如：（以下排序均按order by rootid+(1-sign(rootid))*lybid desc,orderstr,id desc排序）
id rootid orderstr
1 0 空串
2 1 z ??回复根贴，使用串z初始化排序字串
___________________
3 1 y ??回复1，将排在1后面的排序字符串的最后一个字符z的ascii码减1，组成新的排序串。

排序结果为：
id rootid orderstr
1 0 空串
3 1 y
2 1 z
___________________
4 1 x ??回复1贴，排序字符串求法同上

排结果为：
id rootid orderstr
1 0 空串
4 1 x
3 1 y
2 1 z
___________________
5 1 xz ??回复4贴，检查4贴是否已经有回复，现没有，初始化排序串第二个字符（在4后加上z）

排序结果为：
id rootid orderstr
1 0 空串
4 1 x
5 1 xz
3 1 y
2 1 z
___________________
6 1 xy ??回复4贴，将4贴的第一子贴的排序字串最后一个字符的ascii码减1

排序结果为：
id rootid orderstr
1 0 空串
4 1 x
6 1 xy
5 1 xz
3 1 y
2 1 z

这样，根据orderstr和len(orderstr)??深度结合就实现了树状结构。
id orderstr
1 空串
4 x
6 xy
5 xz
3 y
2 z

加贴存储过程：
if exists (select * from sysobjects where id = object_id("lybsave"))
drop proc lybsave
CREATE PROCEDURE [lybsave] @keyid int=0,@guestname varchar(20),@guestitle varchar(100),@guestcomm
text,@guestemail varchar(50)='''',@emailflag bit=0,@fromip varchar(15),@recimail varchar(50) OUTPUT
AS
DECLARE @ostr varchar(30),@rootid int,@lybid int,@ostrs varchar(30),@l tinyint,@tdt datetime,@putdate
varchar(10),@puttime varchar(5),@eflag bit
select @tdt=getdate()
select @putdate=convert(varchar(4),datepart(yy,@tdt))+''-''+left(''0''+convert(varchar(2),datepart(mm,@tdt)),2)
+''-''+left(''0''+convert(varchar(2),datepart(dd,@tdt)),2)
select @puttime=left(''0''+convert(varchar(2),datepart(hh,@tdt)),2)+'':''+left(''0''+convert(varchar(2),datepart
(mi,@tdt)),2)
select @ostr='''',@rootid=0,@lybid=0,@l=0
if (@guestemail='''') select @emailflag=0
If @keyid=0 --发新贴
goto newin
ELSE
begin
SELECT @lybid=lybid,@rootid=rootid,@ostr=orderstr,@recimail=guestemail,@eflag=emailflag from guestbook
where lybid=@keyid
IF @lybid=0 --回复贴没找到，当新贴发表
goto newin
ELSE
BEGIN
if (@eflag=0 and @guestemail<>''swuse@21cn.com abc'') select @recimail='''' --如果是版主回复且指定发邮件给提
问者，则不管发贴者是否要求回复，后面的abc相当于管理密码
if (@rootid=0) select @rootid=@lybid
select @ostrs=@ostr+''%'',@lybid=0
select top 1 @lybid=lybid,@ostrs=orderstr from guestbook where rootid=@rootid and (orderstr like
@ostrs) and lybid<>@keyid order by orderstr
if (@lybid=0) select @ostr=@ostr+char(122)
else
begin
select @l=len(@ostrs)
select @ostr=left(@ostrs,@l-1)+char(ascii(substring(@ostrs,@l,1))-1)
end
goto newin
end
end

newin:
INSERT into guestbook
(guestname,guestitle,guestcomm,putdate,puttime,guestemail,emailflag,rootid,fromip,orderstr) values
(@guestname,@guestitle,@guestcomm,@putdate,@puttime,rtrim(@guestemail),@emailflag,@rootid,@fromip,@ostr)

删贴（剪枝）存储过程：

if exists (select * from sysobjects where id = object_id("lybdel"))
drop proc lybdel
CREATE PROCEDURE [lybdel] @keyid int
AS
DECLARE @ostr varchar(30),@rootid int,@lybid int
select @ostr='',@rootid=0,@lybid=0
SELECT @ostr=orderstr,@rootid=rootid,@lybid=lybid from guestbook where lybid=@keyid
if (@lybid<>0)
BEGIN
if (@rootid=0) select @rootid=@lybid
SELECT @ostr=@ostr+''%''
DELETE FROM guestbook where orderstr like @ostr and rootid=@rootid or lybid=@rootid
END

以下是建立库结构的Sql语句（以一个简单的树状留言薄为例）：

if exists(select * from sysobjects where ID = object_id("guestbook"))
drop table guestbook
go
drop table guestbook
create table guestbook(
lybid int identity(1,1),
guestname varchar(40) NOT NULL,
guestitle varchar(100) NOT NULL,
guestcomm varchar(8000),
putdate varchar(20),
puttime varchar(10),
guestemail varchar(50),
emailflag bit,
rootid int,
fromip varchar(25),
orderstr varchar(3)
)

程序名称：savelyb.asp
程序功能：保存贴子内容


<%
posttype=request("posttype")
pageno=request("pageno")
keyid=request("keyid")
if keyid="" then keyid=0
emailpost=request("emailpost")
guestitle=trim(request.form("guestitle"))
guestname=trim(request.form("guestname"))
guestcomm=rtrim(request.form("guestcomm"))
guestemail=request.form("guestemail")
emailflag=request.form("emailflag")
if guestitle="" or guestname="" then
call errmessage("<center><br><br><font size=4 color=red>资料不完整，留言标题和姓名必须填写
</font><br><br><a href=''javascript:history.back();''>返回重填</a> <a href=''index.asp?pageno="&pageno&"''>
放弃发表</a></center>")
response.end
end if
guestcomm=replace(guestcomm," "," ")
guestcomm=replace(guestcomm,chr(13),"<br>")
if emailflag="on" then
temailflag=1
else
temailflag=0
end if

if session("guestitle")=guestitle then response.redirect "index.asp"
set guestconn=Server.CreateObject("ADODB.connection")
guestconn.Open lybstr
set cm = Server.CreateObject("ADODB.Command")
Set cm.ActiveConnection = guestconn
cm.CommandText = "lybsave"
cm.CommandType = 4
set p = cm.Parameters
p.Append cm.CreateParameter("@keyid",3,1,4)
p.Append cm.CreateParameter("@guestname",202,1,20)
p.Append cm.CreateParameter("@guestitle",202,1,100)
p.Append cm.CreateParameter("@guestcomm",201,1,16000)
p.Append cm.CreateParameter("@guestemail",202,1,50)
p.Append cm.CreateParameter("@emailflag",17,1,1)
p.Append cm.CreateParameter("@fromip",202,1,15)
p.Append cm.CreateParameter("@recimail",202,2,50)

cm("@keyid")=keyid
cm("@guestname")=guestname
cm("@guestitle")=guestitle
cm("@guestcomm")=guestcomm
cm("@guestemail")=ltrim(guestemail)
cm("@emailflag")=temailflag
cm("@fromip")=Request.ServerVariables("REMOTE_ADDR")
cm.execute

if trim(guestemail)="" then guestemail="swuse@21cn.com" ''如果回复的用户没有填写Email地址，则使用版主Email地址
发信
if guestemail="swuse@21cn.com abc" then guestemail="swuse@21cn.com" ''处理版主邮件地址
if cm("@recimail")<>"" then call posttome
call errmessage("<center><br><br><font color=56abff size=4>谢谢你的留言！<br><br></font><a href=''index.asp?
pageno="&pageno&"''>阅读留言</a></center>")
session("guestitle")=guestitle
guestconn.close
set guestconn=nothing
response.end

sub errmessage(message) %>
<html>
<head><title>软件使用留言薄回复</title>
<style TYPE="text/css">

</style>
</head>
<body topmargin=16 background=''topbg.gif''>
<% =message %>
</body>
</html>
<% end sub

sub posttome
guestcomm=replace(guestcomm," "," ")
guestcomm=replace(guestcomm,"<br>","")
Set myMail = Server.CreateObject("CDONTS.NewMail")
myMail.From = guestemail
myMail.To = cm("@recimail")
myMail.Subject = guestitle&"?"&guestname
myMail.body =guestcomm & vbCrLf & vbCrLf & "软件使用留言薄 "
myMail.Send
Set myMail=Nothing
end sub
%>

程序名称：revert.asp
程序功能：回复表单


<%
pageno=request("pageno")
keyid=request("keyid")
posttype=request("type")
if posttype="revert" then
set guestconn=Server.CreateObject("ADODB.connection")
guestconn.Open lybstr
set guestrs=server.createobject("ADODB.recordset")
sqlstr="SELECT * FROM guestbook where lybid="&keyid
guestrs.open sqlstr,guestconn,1,1
if guestrs.eof then response.redirect "index.asp"
end if
%>
<html>
<head><title>软件使用留言薄回复</title>
<style TYPE="text/css">

</style>
</head>
<body topmargin=16>
<formrevert'' method=''POST'' action=''savelyb.asp''>
<input type=hidden name=posttype value=''<% =posttype %>''>
<input type=hidden name=pageno value=<% =pageno %>>
<table width=100% align=center>
<% if posttype="revert" then %>
<input type=hidden name=keyid value=<% =guestrs("lybid") %>>
<tr style="color:red">
<td align=centertd1'' width=70%>留言标题</td>
<td align=center width=70td1''><img src=''http://www.jz123.cn/Article/10/130/UploadFiles/20055925342369.gif''>作者</td>
<td align=center width=100td1''>发表时间</td>
<td align=center width=60td1'' nowrap>贴子字数</td>
<td align=center width=60td1'' nowrap>贴子序号</td>
</tr>
<tr>
<td style="color:red" align=center width=80%><% =guestrs("guestitle") %></td>
<td align=right width=70 nowrap><% =guestrs("guestname") %> </td>
<td width=100 nowrap><% =guestrs("putdate")&" "&guestrs("puttime") %></td>
<td align=right width=60 nowrap><% =len(guestrs("guestcomm")) %> </td>
<td align=right width=60 nowrap><% =guestrs("lybid") %> </td>
</tr>
<tr><td colspan=5 style="background-color:f3F7FF" height=50 valign=top><% =guestrs("guestcomm") %><p
align=right>[From:<% =guestrs("fromip") %>] </p></td></tr>
<% end if %>
<tr><td></td><td width=70></td><td width=100></td><td width=60></td><td width=60></td></tr>
<tr><td height=16 colspan=5 style="background-color:ffffff"></td></tr>
<tr><td> 留言标题：<input type=''text''guestitle'' size=''36'' maxlength=''100''small1''></td>
<td colspan=4> <input type=''checkbox''emailflag''>有回复请通知我</td></tr>
<tr>
<td> 网上昵名：<input type=''text''guestname'' size=36 maxlength=20small1''></td>
<td colspan=4> 邮箱地址：<input type=''text''guestemail'' size=25 maxlength=50
class=''small1''></td>
</tr>
<tr><td colspan=5 valign=top style="background-color:f3F7FF"> 留言内容：<textarea cols=80 rows=6
class=''small1''guestcomm''></textarea></td></tr>
<tr><td colspan=5 align=center><a href="javascript:document.revert.submit();"> 发送留言 </a> <a
href=''index.asp?pageno=<% =pageno %>''> 放弃留言 </a></td></tr>
</table>
</form>
</body>
</html>
<%
if posttype="revert" then
guestrs.close
set guestrs=nothing
guestconn.close
set guestconn=nothing
end if
%>

程序名称：lybcon.inc
程序功能：数据库连接字符串

<%
lybstr="Provider=SQLOLEDB.1;Password=密码;Persist Security Info=True;User ID=sa;Initial Catalog=数据库
名;Data Source=数据库服务器名;Connect Timeout=15"
%>
注意：一时疏忽，请使用.asp作为连接字符串文件名??有些服务器没有为.inc文件指定对应的映射，从而可以看到.inc的文件内容！！

程序名称：delrec.asp
程序功能：删除贴子（剪枝）


<%
flag=request("flag")
keyid=request("keyid")
pageno=request("pageno")
password=request.form("password")
if flag="1" then
if password="你的管理密码" then
set guestconn=Server.CreateObject("ADODB.connection")
guestconn.Open lybstr
set cm = Server.CreateObject("ADODB.Command")
Set cm.ActiveConnection = guestconn
cm.CommandText = "lybdel"
cm.CommandType = 4
set p = cm.Parameters
p.Append cm.CreateParameter("@keyid",3,1,4)
cm("@keyid")=keyid
cm.execute
guestconn.close
set guestconn=nothing
response.redirect "index.asp?pageno="&pageno
end if
end if
%>
<html>
<head><title>软件使用留言薄管理</title>
<style TYPE="text/css">

</style>
</head>
<body topmargin=16 background=''topbg.gif''>
<br><br><br>
<formmanage'' action=''delrec.asp'' method=''post''>
<input type=hiddenflag'' value=''1''>
<input type=hiddenkeyid'' value=''<% =keyid %>''>
<input type=hiddenpageno'' value=''<% =pageno %>''>
<table width=80% align=center>
<tr>
<td align=right>请输入管理密码：</td>
<td align=left><input type=''password''password'' size=20submit''></td>
<td align=center><input type=''submit'' value=''删除该留言''submit''></td>
</tr>
</table>
</form>

程序index.asp
功能：显示目录树

<%
pageno=request("pageno")
searchtype=request("searchtype")
searchnr=rtrim(request("searchnr"))
set guestconn=Server.CreateObject("ADODB.connection")
guestconn.Open lybstr
set guestrs=server.createobject("ADODB.recordset")
sqlstr="SELECT * FROM guestbook"
if searchnr<>"" then sqlstr=sqlstr&" where "&searchtype&" like ''%%"&replace(searchnr,"''","''")&"%%''"
sqlstr=sqlstr&" order by rootid+(1-sign(rootid))*lybid desc,orderstr,lybid desc"
guestrs.open sqlstr,guestconn,1,1
rowcount=25
if pageno="" then pageno=1
if not guestrs.eof then
if IsNumeric(pageno) then
guestrs.pagesize=rowcount
if pageno+1>guestrs.pagecount+1 then pageno=guestrs.pagecount
else
pageno=1
end if
guestrs.AbsolutePage=pageno
else
pageno=1
end if
%><html>
<head><title>软件使用留言薄</title>
<style TYPE="text/css">

</style>
<script language="javascript1.2">
function d(sp,t,a,d,ti,l,id,rid,pn)
{
document.write("<tr><td>"+sp+"<a href=''disprec.asp?
keyid="+id+"&rootid="+rid+"&pageno="+pn+"''>"+t+"</a></td>");
document.write("<td align=right>"+a+" </td>");
document.write("<td nowrap align=center>"+d+" "+ti+"</td>");
document.write("<td align=right>"+l+" </td>");
document.write("<td align=right>"+id+" </td>");
document.write("</tr>");
}
</script>
</head>
<body topmargin=16>
<formindex'' method=''POST'' action=''index.asp''>
<table width=100% align=center>
<tr>
<td align=centerfont-size:16px;background-color:ffffff;color:red'' height=35 valign=top>软件使用
留言薄</td>
<td align=center colspan=3font-size:13px;background-color:ffffff'' valign=''bottom''>共<font
style=''font-size:16px;color:red''><% =guestrs.recordcount %></font>条留言<fontfont-
size:16px;color:red''><% =guestrs.pagecount %></font>页目前第<fontfont-size:16px;color:red''><%
=pageno %></font>页</td>
<tdbackground-color:ffffff'' valign=bottom><a href="/wen/swuselyb/index.asp">旧留言薄
</a></td></tr>
<tr>
<tdtd2''center''>搜索贴子
<selectsearchtype''small1''>
<option value=''guestname'' <% if searchtype="guestname" then response.write("selected") %>>贴子作者
</opion>
<option value=''guestcomm'' <% if searchtype="guestcomm" then response.write("selected") %>>贴子内容
</opion>
</select> <input type=''text''searchnr'' size=14small1'' value=''<% =searchnr %>''> <a
href=''javascript:document.index.submit();''>开始搜索</a>
</td>
<td colspan=3td2''center''><% if pageno+1>2 then %><a
href=''javascript:document.index.pageno.value--;document.index.submit();''>上一页</a><% else %>上一页<% end
if %> <% if pageno+1<guestrs.pagecount+1 then %><a
href=''javascript:document.index.pageno.value++;document.index.submit();''>下一页</a><% else %>下一页<% end
if %> 到<input type=''text''pageno'' size=6small1'' value=<% =pageno %>>页</td>
<tdtd2'' align=center><a href=''revert.asp?type=post''>发贴子</a></td>
</tr>
<tr style="color:red">
<td align=centertd1''>留言标题</td>
<td align=center width=70td1''><img src=''http://www.jz123.cn/Article/10/130/UploadFiles/20055925342369.gif''>作者</td>
<td align=center width=100td1''>发表时间</td>
<td align=center width=60td1'' nowrap>贴子字数</td>
<td align=center width=60td1'' nowrap>贴子序号</td>
</tr>
<%
do while true
if guestrs.eof then exit do
if guestrs("rootid")=0 then exit do
guestrs.movenext
loop
do while not guestrs.eof and rowcount>0 %><script>d(''<% =replace(space(2*len(guestrs
("orderstr")))," "," ") %>'',''<% =guestrs("guestitle") %>'',''<% =guestrs("guestname") %>'',''<% =guestrs
("putdate") %>'',''<% =guestrs("puttime") %>'',<% =len(guestrs("guestcomm")) %>,<% =guestrs("lybid") %>,<%
=guestrs("rootid") %>,<% =pageno %>)</script>
<%
rowcount=rowcount-1
guestrs.movenext
if not guestrs.eof then
if guestrs("rootid")<>0 and rowcount=0 then rowcount=1
end if
loop
guestrs.close
set guestrs=nothing
guestconn.close
set guestconn=nothing
%>
</table>
</form>
<br><br><br>
</body>
</html>

程序：disprec.asp
功能：显示贴子具体内容


<%
keyid=request("keyid")
rootid=request("rootid")
pageno=request("pageno")
if rootid=0 then rootid=keyid
set guestconn=Server.CreateObject("ADODB.connection")
guestconn.Open lybstr
set guestrs=server.createobject("ADODB.recordset")
sqlstr="SELECT * FROM guestbook where lybid="&rootid&" or rootid="&rootid
sqlstr=sqlstr&" order by orderstr,lybid desc"
guestrs.open sqlstr,guestconn,1,1
%>
<html>
<head><title>软件使用留言薄</title>
<style TYPE="text/css">

</style>
</head>
<body topmargin=16 background=''topbg.gif''>
<formindex'' method=''POST'' action=''index.asp''>
<table width=100% align=center>
<tr>
<td align=centerfont-size:16px;background-color:ffffff;color:red'' height=35 valign=top>软件使用
留言薄</td>
<td align=center colspan=2background-color:ffffff'' valign=''bottom''>相关留言共<fontfont-
size:16px;color:red''><% =guestrs.recordcount %></font>条</td>
<tdbackground-color:ffffff'' valign=bottom align=center><a href=''index.asp?pageno=<% =pageno %>''>
留言列表</a></td>
<tdbackground-color:ffffff'' valign=bottom align=center><a href=''revert.asp?type=post''>发贴子
</a></td>
</tr>
<tr style="color:red">
<td align=centertd1'' width=70%>留言标题</td>
<td align=center width=70td1'' nowrap><img src=''http://www.jz123.cn/Article/10/130/UploadFiles/20055925342369.gif''>作者</td>
<td align=center width=100td1'' nowrap>发表时间</td>
<td align=center width=60td1'' nowrap>贴子字数</td>
<td align=center width=60td1'' nowrap>贴子序号</td>
</tr>
<% do while not guestrs.eof %>
<tr>
<td><% if guestrs("rootid")<>0 then response.write(replace(space(len(guestrs("orderstr")))," "," ")) %
><% if guestrs("lybid")+1=keyid+1 then
if guestrs("rootid")=0 then
rootid=guestrs("lybid")
else
rootid=guestrs("rootid")
end if
lybid=guestrs("lybid")
guestitle=guestrs("guestitle")
guestname=guestrs("guestname")
guestemail=guestrs("guestemail")
guestcomm=guestrs("guestcomm")
putdate=guestrs("putdate")
puttime=guestrs("puttime")
emailflag=guestrs("emailflag")
%><% =guestrs("guestitle") %><% else %><a href=''disprec.asp?keyid=<% =guestrs("lybid") %>&rootid=<%
=guestrs("rootid") %>&pageno=<% =pageno %>''><% =guestrs("guestitle") %></a><% end if %></td>
<td align=right nowrap><% =guestrs("guestname") %> </td>
<td nowrap align=center nowrap><% =guestrs("putdate")&" "&guestrs("puttime") %></td>
<td align=right nowrap><% =len(guestrs("guestcomm")) %> </td>
<td align=right nowrap><% =guestrs("lybid") %> </td>
</tr>
<%
guestrs.movenext
loop
guestrs.close
set guestrs=nothing
guestconn.close
set guestconn=nothing
%>
<tr><td height=20 colspan=5 style="background-color:ffffff"></td></tr>
<tr>
<td style="color:red" align=center><% =guestitle %></td>
<td align=right width=70><% =guestname %> </td>
<td width=100><% =putdate&" "&puttime %></td>
<td align=right width=60><% =len(guestcomm) %> </td>
<td align=right width=60><% =lybid %> </td>
</tr>
<tr><td colspan=5 style="background-color:f3F7FF" height=50 valign=top><% =guestcomm %><p align=right><a
href=''delrec.asp?type=revert&keyid=<% =keyid %>&pageno=<% =pageno %>''>删除留言</a> <a href=''revert.asp?
type=revert&keyid=<% =lybid %>&pageno=<% =pageno %>''>回复作者</a> </td></tr>
</table>
</form>
</body>
</html>