(我以前发表的一篇旧文章,现在统一整理到这里来。)
SQL Server 2000 中用于建表以及其他脚本的 SQL 如下:
/**/
/* -------------------------------------
功能: 建立表 tree 以及两个自定义函数
用于层次性的输出到 <select>
Author: Neil Chen(木野狐)
Date: 2004-3-19
---------------------------------------*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.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)
![](/Images/OutliningIndicators/ExpandedBlockStart.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)
![](/Images/OutliningIndicators/ExpandedBlockStart.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)
![](/Images/OutliningIndicators/ExpandedBlockStart.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)
![](/Images/OutliningIndicators/ExpandedBlockStart.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"
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>"
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>"
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/ContractedBlock.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/ContractedBlock.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/ContractedBlock.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/ContractedBlock.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/ContractedBlock.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/ContractedBlock.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)