[ASP] 在下拉列表中输出树型结构(原作)
(我以前发表的一篇旧文章,现在统一整理到这里来。)
SQL Server 2000 中用于建表以及其他脚本的 SQL 如下:
/* -------------------------------------
功能: 建立表 tree 以及两个自定义函数
用于层次性的输出到 <select>
Author: Neil Chen(木野狐)
Date: 2004-3-19
---------------------------------------*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
/* 创建表 */
use test
go
![](/Images/OutliningIndicators/None.gif)
DROP TABLE tree
DROP FUNCTION [dbo].getDepth
DROP FUNCTION [dbo].getCode
go
![](/Images/OutliningIndicators/None.gif)
create table tree
(
id int identity primary key,
pid int,
title varchar(50)
)
![](/Images/OutliningIndicators/None.gif)
/* 初始化数据 */
insert tree select 0, '中国'
insert tree select 1, '江苏'
insert tree select 1, '山东'
insert tree select 2, '南京'
insert tree select 2, '无锡'
insert tree select 1, '四川'
![](/Images/OutliningIndicators/None.gif)
go
![](/Images/OutliningIndicators/None.gif)
/* ------------------------------
函数: getDepth
功能: 得到深度, 根的深度为 0
------------------------------ */
CREATE FUNCTION [dbo].getDepth(@id INT)
returns INT
AS
BEGIN
![](/Images/OutliningIndicators/None.gif)
DECLARE @depth int, @temp int
SET @depth = 0
![](/Images/OutliningIndicators/None.gif)
WHILE (SELECT pid FROM tree WHERE id = @id) > 0
BEGIN
SELECT @id = pid FROM tree WHERE id = @id
SET @depth = @depth + 1
END
![](/Images/OutliningIndicators/None.gif)
RETURN @depth
![](/Images/OutliningIndicators/None.gif)
END
![](/Images/OutliningIndicators/None.gif)
go
![](/Images/OutliningIndicators/None.gif)
/* ------------------------------
函数: getCode
功能: 得到编码
------------------------------ */
CREATE FUNCTION [dbo].getCode(@id int)
RETURNs VARCHAR(10)
AS
![](/Images/OutliningIndicators/None.gif)
BEGIN
![](/Images/OutliningIndicators/None.gif)
DECLARE @code VARCHAR(10)
SET @code = ''
![](/Images/OutliningIndicators/None.gif)
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
![](/Images/OutliningIndicators/None.gif)
SELECT @id = pid FROM tree WHERE id = @id
END
![](/Images/OutliningIndicators/None.gif)
RETURN @code
![](/Images/OutliningIndicators/None.gif)
END
go
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
/* 测试 */
SELECT *, [dbo].getCode(id) AS code
FROM tree
ORDER BY code
![](/Images/OutliningIndicators/None.gif)
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>
![](/Images/OutliningIndicators/None.gif)
<BODY>
![](/Images/OutliningIndicators/None.gif)
<%
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"
![](/Images/OutliningIndicators/None.gif)
dim sql, rs
sql = "SELECT *, [dbo].getCode(id) AS code from tree order by code"
set rs = conn.execute(sql)
![](/Images/OutliningIndicators/None.gif)
response.write "<select>"
![](/Images/OutliningIndicators/None.gif)
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
![](/Images/OutliningIndicators/None.gif)
response.write "</select>"
![](/Images/OutliningIndicators/None.gif)
rs.close : set rs = nothing
conn.close : set conn = nothing
%>
![](/Images/OutliningIndicators/None.gif)
</BODY>
</HTML>
SQL Server 2000 中用于建表以及其他脚本的 SQL 如下:
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
对应的用于输出的 ASP 程序:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)