这篇博客介绍了如何在SQL Server 2000中建立表`tree`以及两个自定义函数`getDepth`和`getCode`，用于层次性地输出到HTML `<select>`下拉列表中。作者提供了创建表和初始化数据的SQL脚本，以及生成深度和编码的函数定义。最后展示了如何用ASP程序将这些数据输出到下拉列表中，实现树型结构的显示。
（我以前发表的一篇旧文章，现在统一整理到这里来。）
SQL Server 2000 中用于建表以及其他脚本的 SQL 如下：
/**//* -------------------------------------
功能： 建立表 tree 以及两个自定义函数
用于层次性的输出到 <select>
Author: Neil Chen（木野狐）
Date: 2004-3-19
---------------------------------------*/
/**//* 创建表 */
use test
go
DROP TABLE tree
DROP FUNCTION [dbo].getDepth
DROP FUNCTION [dbo].getCode
go
create table tree
(
id int identity primary key,
pid int,
title varchar(50)
)
/**//* 初始化数据 */
insert tree select 0, '中国'
insert tree select 1, '江苏'
insert tree select 1, '山东'
insert tree select 2, '南京'
insert tree select 2, '无锡'
insert tree select 1, '四川'
go
/**//* ------------------------------
函数： getDepth
功能： 得到深度, 根的深度为 0
------------------------------ */
CREATE FUNCTION [dbo].getDepth(@id INT)
returns INT
AS
BEGIN
DECLARE @depth int, @temp int
SET @depth = 0
WHILE (SELECT pid FROM tree WHERE id = @id) > 0
BEGIN
SELECT @id = pid FROM tree WHERE id = @id
SET @depth = @depth + 1
END
RETURN @depth
END
go
/**//* ------------------------------
函数： getCode
功能： 得到编码
------------------------------ */
CREATE FUNCTION [dbo].getCode(@id int)
RETURNs VARCHAR(10)
AS
BEGIN
DECLARE @code VARCHAR(10)
SET @code = ''
WHILE @id > 0
BEGIN
SELECT @code = CAST(COUNT(id) AS VARCHAR(10)) + @code
FROM tree
WHERE [dbo].getDepth(id) = [dbo].getDepth(@id)
AND id <= @id
SELECT @id = pid FROM tree WHERE id = @id
END
RETURN @code
END
go
/**//* 测试 */
SELECT *, [dbo].getCode(id) AS code
FROM tree
ORDER BY code
go
对应的用于输出的 ASP 程序：
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="Neil Chen（木野狐）">
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</HEAD>
<BODY>
<%
dim conn
set conn = server.CreateObject("ADODB.Connection")
conn.open "Provider=SQLOLEDB.1;Password=markchen;Persist Security Info=True;User ID=sa;Initial Catalog=test;Data Source=cr"
dim sql, rs
sql = "SELECT *, [dbo].getCode(id) AS code from tree order by code"
set rs = conn.execute(sql)
response.write "<select>"
do while not rs.eof
response.write "<option>"
'输出缩进
for i = 2 to len(rs("code"))
response.write " "
next
response.write rs("title") & " " & rs("code") & "</option>"
rs.MoveNext
loop
response.write "</select>"
rs.close : set rs = nothing
conn.close : set conn = nothing
%>
</BODY>
</HTML>
SQL Server 2000 中用于建表以及其他脚本的 SQL 如下：
/**//* -------------------------------------功能： 建立表 tree 以及两个自定义函数 用于层次性的输出到 <select>Author: Neil Chen（木野狐）Date: 2004-3-19---------------------------------------*//**//* 创建表 */use testgoDROP TABLE treeDROP FUNCTION [dbo].getDepthDROP FUNCTION [dbo].getCodegocreate table tree( id int identity primary key, pid int, title varchar(50))/**//* 初始化数据 */insert tree select 0, '中国'insert tree select 1, '江苏'insert tree select 1, '山东'insert tree select 2, '南京'insert tree select 2, '无锡'insert tree select 1, '四川'go/**//* ------------------------------函数： getDepth功能： 得到深度, 根的深度为 0 ------------------------------ */CREATE FUNCTION [dbo].getDepth(@id INT)returns INT AS BEGIN DECLARE @depth int, @temp intSET @depth = 0WHILE (SELECT pid FROM tree WHERE id = @id) > 0BEGIN SELECT @id = pid FROM tree WHERE id = @id SET @depth = @depth + 1END RETURN @depthEND go /**//* ------------------------------函数： getCode功能： 得到编码------------------------------ */CREATE FUNCTION [dbo].getCode(@id int)RETURNs VARCHAR(10)AS BEGIN DECLARE @code VARCHAR(10) SET @code = ''WHILE @id > 0BEGIN SELECT @code = CAST(COUNT(id) AS VARCHAR(10)) + @code FROM tree WHERE [dbo].getDepth(id) = [dbo].getDepth(@id) AND id <= @id SELECT @id = pid FROM tree WHERE id = @idEND RETURN @codeEND go /**//* 测试 */SELECT *, [dbo].getCode(id) AS codeFROM treeORDER BY codego对应的用于输出的 ASP 程序：
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD><TITLE> New Document </TITLE><META NAME="Generator" CONTENT="EditPlus"><META NAME="Author" CONTENT="Neil Chen（木野狐）"><meta http-equiv="Content-Type" content="text/html; charset=gb2312"></HEAD><BODY><%dim connset conn = server.CreateObject("ADODB.Connection")conn.open "Provider=SQLOLEDB.1;Password=markchen;Persist Security Info=True;User ID=sa;Initial Catalog=test;Data Source=cr"dim sql, rssql = "SELECT *, [dbo].getCode(id) AS code from tree order by code"set rs = conn.execute(sql)response.write "<select>"do while not rs.eof response.write "<option>" '输出缩进 for i = 2 to len(rs("code")) response.write " " next response.write rs("title") & " " & rs("code") & "</option>" rs.MoveNextloopresponse.write "</select>"rs.close : set rs = nothingconn.close : set conn = nothing%></BODY></HTML>
扫一扫
151
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
