无限分类算法

根据国外的: Nested Set Model of Trees
http://searchoracle.techtarget.com/tip/1,289483,sid13_gci537290,00.html
http://www.dbmsmag.com/9603d06.html

我觉得奇怪的是，这个SQL来实现树的算法早在1996、1999年就提出了，而为什么在国内的ASP系统中都没有见过用此来实现的？其实此算法的无限级才是真正是“无限”即结点数目可以达2^32=4G个（只局限于lft和rgt的取值范围），而且更新树的时候，只需要维护lft和rgt两个字段的整型数据（要知道整型数据的运算速度是最快的）。

而目前国内大家都是用的一个字符串字段来保存结点的路径，SQL对可索引字符串的长度是有限（Access:255,MSSQL:8000），但如果用备注类型来做的话，就根本没得什么索引和效率可言了。

演示测试地址:

http://www.lxasp.com/Test_NestedTree.asp

'数据表如下:
'CREATE TABLE [TreeNode] (
'  [tn_id] [int] IDENTITY (1, 1) NOT NULL ,
'  [tn_name] [nvarchar] (50) NULL ,
'  [tn_lft] [int] NULL ,
'  [tn_rgt] [int] NULL ,
'  CONSTRAINT [PK_TreeNode] PRIMARY KEY  CLUSTERED
'  (
'    [tn_id]
'  )  ON [PRIMARY]
') ON [PRIMARY]
'GO

Public startime,endtime,conn,connstr,rs,sql,sqlcount
startime=Timer()
sqlcount=0

Set conn = Server.CreateObject("ADODB.Connection")

connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("test.mdb")
conn.Open connstr

'############################################################
'* 名称: 插入结点到树
'* 说明:
'* 日期: 2006-09-20  作者: pkmaster
'############################################################
Function InsertNew(Parent_ID , NewName)
 
  Dim p_lft,p_rgt
 
  SQL = "SELECT * FROM TreeNode WHERE tn_id="&Parent_ID
  Set rs=Server.CreateObject("ADODB.Recordset")
  rs.Open SQL,conn,1,3 '11 for Read '13 for Write
  sqlcount=sqlcount+1
  If rs.EOF And rs.BOF Then
    '不存在Parent_ID，则出错
  Else
    rs.MoveFirst
   
    p_lft=rs("tn_lft")
    p_rgt=rs("tn_rgt")
   
    conn.Execute "UPDATE TreeNode SET tn_rgt=tn_rgt+2 WHERE tn_rgt>" & CStr(p_rgt-1)
    sqlcount=sqlcount+1
    conn.Execute "UPDATE TreeNode SET tn_lft=tn_lft+2 WHERE tn_lft>" & CStr(p_rgt-1)
    sqlcount=sqlcount+1
   
    rs.AddNew
    rs("tn_lft")=p_rgt
    rs("tn_rgt")=p_rgt+1
    rs("tn_name")=NewName
    rs.Update
    InsertNew=p_rgt+1
  End If
  rs.Close
  Set rs = Nothing
 
End Function

'############################################################
'* 名称: 显示整棵树
'* 说明:
'* 日期: 2006-09-20  作者: pkmaster
'############################################################
Function ShowTree()
 
  Dim stackd,stkpos,STACKMAX
  Dim i,j
  i=0
  j=0
  STACKMAX=100
  stkpos=0
 
  SQL = "SELECT * FROM TreeNode ORDER BY tn_lft ASC"
  Set rs=Server.CreateObject("ADODB.Recordset")
  rs.Open SQL,conn,1,1 '11 for Read '13 for Write
  sqlcount=sqlcount+1
  If rs.EOF And rs.BOF Then
   
  Else
   
    ReDim stackd(0,STACKMAX)
   
    rs.MoveFirst
    Do Until rs.EOF

      If stkpos=0 Then
        '至少要有一个结点，如果是网站，那么就以该网站的名称作为根结点
        Response.Write rs("tn_name") & vbCrLf
      End If
     
      If stkpos>0 Then
       
        '出栈
        Do While stackd(0,stkpos-1)<rs("tn_rgt")
          stkpos=stkpos-1
        Loop

        Response.Write Space(stkpos*4) & "(" & rs("tn_id") & ")" & rs("tn_name") & vbCrLf

      End If
     
      '进栈
      stkpos=stkpos+1
      If stkpos-1>STACKMAX Then ReDim Preserve stackd(0,stkpos-1+STACKMAX)
      stackd(0,stkpos-1)=rs("tn_rgt")

      rs.MoveNext
    Loop

  End If
  rs.Close
  Set rs = Nothing

End Function

'############################################################
'* 名称: 删除结点以及它的子树
'* 说明:
'* 日期: 2006-09-20  作者: pkmaster
'############################################################
Function DeleteNode(NodeID)
 
  Dim lft,rgt,diff
 
  SQL = "SELECT * FROM TreeNode WHERE tn_id="&NodeID
  Set rs=Server.CreateObject("ADODB.Recordset")
  rs.Open SQL,conn,1,1 '11 for Read '13 for Write
  sqlcount=sqlcount+1
  If rs.EOF And rs.BOF Then
    Exit Function
  Else
    rs.MoveFirst
    lft=rs("tn_lft")
    rgt=rs("tn_rgt")
  End If
  rs.Close
  Set rs = Nothing
 
  diff=rgt-lft+1
 
  conn.Execute "DELETE FROM TreeNode WHERE tn_lft>=" & lft & " AND tn_rgt<= " & rgt & " "
  conn.Execute "UPDATE TreeNode SET tn_lft=tn_lft-" & diff & " WHERE tn_lft>" & lft & " "
  conn.Execute "UPDATE TreeNode SET tn_rgt=tn_rgt-" & diff & " WHERE tn_rgt>=" & rgt & " "
 
End Function

'############################################################
'* 名称: 获得某结点的路径
'* 说明:
'* 日期: 2006-09-20  作者: pkmaster
'############################################################
Function GetNodePath(NodeID)
 
  Dim lft,rgt
 
  GetNodePath=""
 
  SQL = "SELECT A.tn_name,A.tn_lft,A.tn_rgt FROM TreeNode A ,TreeNode B WHERE (A.tn_lft<=B.tn_lft) AND (A.tn_rgt>=B.tn_rgt) AND B.tn_id=" & NodeID & " ORDER BY A.tn_lft"
  Set rs=Server.CreateObject("ADODB.Recordset")
  rs.Open SQL,conn,1,1 '11 for Read '13 for Write
  sqlcount=sqlcount+1
  If rs.EOF And rs.BOF Then
   
  Else
    rs.MoveFirst
    Do Until rs.EOF
     
      Response.Write rs(0).Value & "/"
     
      rs.MoveNext
    Loop
  End If
  rs.Close
  Set rs = Nothing

End Function

'############################################################
'* 名称: 获得某结点的直属子结点
'* 说明:
'* 日期: 2006-09-20  作者: pkmaster
'############################################################
Function GetChildNodes(RootID)

  Dim tmpview
  '如果为了提速，可以将下面的SQL语句作为视图
  tmpview="SELECT a.tn_id AS pnt_id, a.tn_name AS pnt_name, b.tn_id AS sub_id, b.tn_name AS sub_name, b.tn_lft, b.tn_rgt " & _
  "FROM TreeNode AS a, TreeNode AS b " & _
  "WHERE b.tn_lft BETWEEN a.tn_lft AND a.tn_rgt AND NOT EXISTS " & _
  "(SELECT * FROM TreeNode AS c " & _
  "WHERE c.tn_lft BETWEEN a.tn_lft AND a.tn_rgt  " & _
  "AND b.tn_lft  BETWEEN c.tn_lft AND c.tn_rgt  " & _
  "AND c.tn_id NOT IN (a.tn_id,b.tn_id) ) "

  SQL="SELECT DISTINCT s1.sub_id,s1.sub_name " & _
  "FROM (" & tmpview & ") as s1,(" & tmpview & ") as s2,TreeNode as o1 " & _
  "WHERE s1.pnt_id=o1.tn_id " & _
  "AND s2.pnt_id=s1.pnt_id " & _
  "AND s1.sub_id<>s2.sub_id " & _
  "AND s2.tn_lft <= s1.tn_lft " & _
  "AND o1.tn_id=" & RootID & " "

  Set rs=Server.CreateObject("ADODB.Recordset")
  rs.Open SQL,conn,1,1
  sqlcount=sqlcount+1
  If rs.EOF And rs.BOF Then
   
  Else
    rs.MoveFirst
    Do Until rs.EOF
     
      Response.Write "(" & rs(0) & ")" & rs(1) & vbCrLf
     
      rs.MoveNext
    Loop
  End If
  rs.Close
  Set rs = Nothing
 
End Function