[SQL Server]树形结构的创建
对于SQL Server来说，构建显示一个树形结构不是一件容易的事情，逻辑构造能力不是它的强项。不过也不是说它没有能力干这个事情，只要换一种思维方式就可以理解它的工作原理。例如，现在有一张表的内容如下：
CategoryNO CategoryName Parent
---------- -------------------------------------------------- ------
0 ROOT NULL
1 .NET 0
2 DataBase 0
3 Java 0
4 Others 0
5 WindowsOS 0
6 F# 1
7 C# 1
8 WPF 1
9 VB.NET 1
10 SQL Server 2
11 J2SE 3
12 批处理 5
13 注册表 5
14 SliverLight 8
15 基本命令 12
16 扩展命令 12
17 HKLM 13
18 HKCU 13
19 DIR 15
20 COPY 15
21 DEL 15
22 IE 5
23 LINQ 1
24 C++ 0
它看上去是多么混乱无序，我们希望它能按如下方式显示，也就是所谓的树形结构：
CategoryNOCategoryName
----------- --------------------
1 .NET
6 F#
7 C#
8 WPF
14 SliverLight
9 VB.NET
23 LINQ
2 DataBase
10 SQL Server
3 Java
11 J2SE
4 Others
5 WindowsOS
12 批处理
15 基本命令
19 DIR
20 COPY
21 DEL
16 扩展命令
13 注册表
17 HKLM
18 HKCU
22 IE
24 C++
至少这样看上去好多了。现在来看看如何实现这个功能。
首先我们需要一个变量来记录当前进入到树形结构的哪个级别，并把它设置为0，表示第一个级别；以及另一个变量来记录当前在对哪条记录操作。
DECLARE @CategoryNO int, @Level int
SET @Level = 0
然后要建立两张临时表，第一张表用来存储待处理记录，第二张表存储最终的结果。关于它们是如何使用的请继续往下看。
CREATE TABLE #TreeViewTemp
(
CategoryNO int NOT NULL,
CategoryName nvarchar(30) NOT NULL,
Parent int NULL,
int NOT NULL
)
CREATE TABLE #TreeViewResult
(
CategoryNO int NOT NULL,
CategoryName nvarchar(30) NOT NULL
)
接下来向#TreeViewTemp表中插入第一级别的记录。在这里，ROOT记录表示的是根级别，是所有第一级别的父级，最终结果将不包含该记录。注意#TreeViewTemp表中记录了这些记录的级别。
INSERT #TreeViewTemp
SELECT CategoryNO, CategoryName, Parent, @Level
FROM Category
WHERE Parent = 0
再下来，进入一个循环结构。循环结束的条件是#TreeViewTemp表中不再有记录。接下来的内容都是在循环结构中的，BEGIN和END关键字就不写出来了。
WHILE EXISTS (SELECT CategoryNO FROM #TreeViewTemp)
循环的第一条语句，取出#TreeViewTemp中当前级别的第一条记录，并记录下它的CategoryNO（还记得一开始的@CategoryNO和@Level变量吗？）
SELECT TOP(1) @CategoryNO = CategoryNO
FROM #TreeViewTemp
WHERE = @Level
ORDER BY CategoryNO
如果取不到记录，也就是说临时表中当前级别的记录不存在，那么令@Level变量的值减一，也就是退回上一级别，并继续下一个循环。
IF @@ROWCOUNT = 0
BEGIN
SET @Level = @Level - 1
CONTINUE
END
如果当前级别还有记录，就把这条记录插入到最终结果的表中。插入的时候根据当前级别在名称前面加上空格。
INSERT #TreeViewResult
SELECT CategoryNO, SPACE(4 * @Level) + CategoryName
FROM #TreeViewTemp
WHERE CategoryNO = @CategoryNO
接着找出刚刚那条记录的所有子类别，插入到#TreeViewTemp表中。这里把@Level的值加1再插入到表中，表明这些记录是下一级别的。
INSERT #TreeViewTemp
SELECT CategoryNO, CategoryName, Parent, @Level + 1
FROM Category
WHERE Parent = @CategoryNO
如果这条记录有子类别，那么就使@Level的值加1，进入下一级别。
IF @@ROWCOUNT0
SET @Level = @Level + 1
循环结构中最后一条语句，把#TreeViewTemp中刚刚处理的那条记录删除。
DELETE #TreeViewTemp
WHERE CategoryNO = @CategoryNO
最后一件事，当然是把最终的结果显示出来了。
SELECT CategoryNO, CategoryName FROM #TreeViewResult
最最后的，把临时表删除。
DROP TABLE #TreeViewTemp
DROP TABLE #TreeViewResult
好了，构建树形结构的基本框架就是这样，可以在这个基础上作些修改以适应不同的需求。
我不知道以上说明是否能让大家明白这个逻辑，甚至我自己也说不清楚，它实在是比较复杂……
这个方法有一个缺点，就是使用了临时表。由于临时表的数据是存储在硬盘中的，所以整个过程的速度会有影响。
在最后把整个过程的代码整合在一起：
Code
DECLARE @CategoryNO int, @Level int
SET @Level = 0
CREATE TABLE #TreeViewTemp
(
CategoryNO int NOT NULL,
CategoryName nvarchar(30) NOT NULL,
Parent int NULL,
int NOT NULL
)
CREATE TABLE #TreeViewResult
(
CategoryNO int NOT NULL,
CategoryName nvarchar(30) NOT NULL
)
INSERT #TreeViewTemp
SELECT CategoryNO, CategoryName, Parent, @Level
FROM Category
WHERE Parent = 0
WHILE EXISTS (SELECT CategoryNO FROM #TreeViewTemp)
BEGIN
SELECT TOP(1) @CategoryNO = CategoryNO
FROM #TreeViewTemp
WHERE = @Level
ORDER BY CategoryNO
IF @@ROWCOUNT = 0
BEGIN
SET @Level = @Level - 1
CONTINUE
END
INSERT #TreeViewResult
SELECT CategoryNO, SPACE(4 * @Level) + CategoryName
FROM #TreeViewTemp
WHERE CategoryNO = @CategoryNO
INSERT #TreeViewTemp
SELECT CategoryNO, CategoryName, Parent, @Level + 1
FROM Category
WHERE Parent = @CategoryNO
IF @@ROWCOUNT0
SET @Level = @Level + 1
DELETE #TreeViewTemp
WHERE CategoryNO = @CategoryNO
END
SELECT CategoryNO, CategoryName FROM #TreeViewResult
DROP TABLE #TreeViewTemp
DROP TABLE #TreeViewResult
页:
[1]