在下拉列表中输出树型结构
原文出处：http://blog.mvpcn.net/inelm/articles/464.aspx
tree.sql
------------------------------------
/* -------------------------------------
功能： 建立表 tree 以及两个自定义函数
用于层次性的输出到 <select>
Author: inelm(csdn)
Email: inelm@msn.com
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
第二个文件： treeSelect.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="Chen Rong, csdn: inelm(Archimond), chenrong2003@hotmail.com">
<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>
效果演示：
http://www.adr.gov.cn/download/chenrong/tree.jpg
选项里面后面的那些数字是为了表示层次关系而特意输出的， 正式应用的时候去掉就可以了。
