[SQL Server]树形结构的创建

peiyuan1030

　　对于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
　　它看上去是多么混乱无序，我们希望它能按如下方式显示，也就是所谓的树形结构：
　　CategoryNO  CategoryName
----------- --------------------
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,
[Level] 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] = @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 @@ROWCOUNT  0
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,
[Level] 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] = @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 @@ROWCOUNT  0
  SET @Level = @Level + 1

DELETE #TreeViewTemp
WHERE CategoryNO = @CategoryNO
END
SELECT CategoryNO, CategoryName FROM #TreeViewResult
DROP TABLE #TreeViewTemp
DROP TABLE #TreeViewResult