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

实现原理：以一排序字段（字符型实现排序），该字段的实际长度即为回复深度（用一位字符代表一层深度时）。

所受限制：回复深度只受排序串定义长度的限制（有点象空间换深度），每贴回复数（包括根贴和子贴）为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

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

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

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

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>

<!--

a { text-decoration: none}

body {line-height:18px;font-size:10.5pt;font-family:宋体}

a:hover {color:#FF0000;text-decoration:none}

-->

</style>

</head>

<% =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 & "软件使用留言薄 http://swuse.yeah.net"

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>

<!--

a { text-decoration: none}

a:hover {color:#FF0000}

.submit {line-height:9pt;font-size:9pt;font-family:宋体}

td {font-family: 宋体; font-size: 9pt;background-color:ECF7FF}

.td1 {font-family: 宋体; font-size: 9pt;background-color:A2C8F2}

.td2 {font-family: 宋体; font-size: 9pt;background-color:E9EDE0}

.small1 {font-family: 宋体; font-size: 9pt;background-color:ffffff;line-height:9pt}

-->

</style>

</head>

<% if posttype="revert" then %>

</tr>

<tr>

</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 %>

<td colspan=4> <input type='checkbox' name='emailflag'>有回复请通知我</td></tr>

<tr>

<td colspan=4> 邮箱地址：<input type='text' name='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' name='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

<html>

<head><title>软件使用留言薄管理</title>

<!--

a { text-decoration: none}

body {line-height:18px;font-size:9pt;font-family:宋体}

a:hover {color:#FF0000;text-decoration:none}

.submit {line-height:9pt;font-size:9pt;font-family:宋体}

.submit1 {line-height:8pt;font-size:8pt;font-family:宋体}

.selectform {font-family: 宋体; font-size: 9pt;background-color:ffE4E4}

td {font-family: 宋体; font-size: 9pt;background-color:ECF7FF}

.td1 {font-family: 宋体; font-size: 9pt;background-color:A2C8F2}

.td2 {font-family: 宋体; font-size: 9pt;background-color:E9EDE0}

.small1 {font-family: 宋体; font-size: 9pt;background-color:ffffff;line-height:9pt}

-->

</style>

</head>

<tr>

<td align=right>请输入管理密码：</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>

<!--

a { text-decoration: none}

body {line-height:18px;font-size:9pt;font-family:宋体}

a:hover {color:#FF0000;text-decoration:none}

.submit {line-height:9pt;font-size:9pt;font-family:宋体}

.submit1 {line-height:8pt;font-size:8pt;font-family:宋体}

.selectform {font-family: 宋体; font-size: 9pt;background-color:ffE4E4}

td {font-family: 宋体; font-size: 9pt;background-color:ECF7FF}

.td1 {font-family: 宋体; font-size: 9pt;background-color:A2C8F2}

.td2 {font-family: 宋体; font-size: 9pt;background-color:E9EDE0}

.small1 {font-family: 宋体; font-size: 9pt;background-color:ffffff;line-height:9pt}

-->

</style>

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>

<tr>

<td align=center style='font-size:16px;background-color:ffffff;color:red' height=35 valign=top>软件使用

留言薄</td>

<td align=center colspan=3 style='font-size:13px;background-color:ffffff' valign='bottom'>共<font

style='font-size:16px;color:red'><% =guestrs.recordcount %></font>条留言<font style='font-

size:16px;color:red'><% =guestrs.pagecount %></font>页目前第<font style='font-size:16px;color:red'><%

=pageno %></font>页</td>

<td style='background-color:ffffff' valign=bottom><a href="/wen/swuselyb/index.asp">旧留言薄

</a></td></tr>

<tr>

<td class='td2' align='center'>搜索贴子

<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' name='searchnr' size=14 class='small1' value='<% =searchnr %>'> <a

href='javascript:document.index.submit();'>开始搜索</a>

</td>

<td colspan=3 class='td2' align='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' name='pageno' size=6 class='small1' value=<% =pageno %>>页</td>

</tr>

</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>

</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>

<!--

a { text-decoration: none}

body {line-height:18px;font-size:9pt;font-family:宋体}

a:hover {color:#FF0000;text-decoration:none}

td {font-family: 宋体; font-size: 9pt;background-color:ECF7FF}

.td1 {font-family: 宋体; font-size: 9pt;background-color:A2C8F2}

.td2 {font-family: 宋体; font-size: 9pt;background-color:E9EDE0}

.small1 {font-family: 宋体; font-size: 9pt;background-color:ffffff;line-height:9pt}

-->

</style>

</head>

<tr>

<td align=center style='font-size:16px;background-color:ffffff;color:red' height=35 valign=top>软件使用

留言薄</td>

<td align=center colspan=2 style='background-color:ffffff' valign='bottom'>相关留言共<font style='font-

size:16px;color:red'><% =guestrs.recordcount %></font>条</td>

留言列表</a></td>

<td style='background-color:ffffff' valign=bottom align=center><a href='revert.asp?type=post'>发贴子

</a></td>

</tr>

</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>

</tr>

guestrs.movenext

loop

guestrs.close

set guestrs=nothing

guestconn.close

set guestconn=nothing

<tr>

</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>