[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

它看上去是多么混乱无序，我们希望它能按如下方式显示，也就是所谓的树形结构：

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

 

好了，构建树形结构的基本框架就是这样，可以在这个基础上作些修改以适应不同的需求。

我不知道以上说明是否能让大家明白这个逻辑，甚至我自己也说不清楚，它实在是比较复杂……

这个方法有一个缺点，就是使用了临时表。由于临时表的数据是存储在硬盘中的，所以整个过程的速度会有影响。

在最后把整个过程的代码整合在一起：

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