1. 引言
现实项目中经常遇到需要处理递归父子关系的问题，如果把层次关系分开，放在多个表里通过主外键关系联接，最明显的问题就是扩展起来不方便，对于这种情况，一般我们会创建一个使用自连接的表来存放数据。例如存放会员地区数据表结构可能是这样：
| 列名 | 描述 |
|---|---|
| location_id | 地区编号 |
| location_name | 地区名称 |
| parentlocation_id | 上级地区编号 |
或者某个部分的职员表结构可能如下所示：
| 列名 | 描述 |
|---|---|
| employee_id | 职员编号 |
| employee_name | 职员名称 |
| manager_id | 职员的直接上级管理者,和employee_id进行自联接 |
通过类似表结构，我们就可以通过一个表理论上管理无限级数的父/子关系，但是当我们需要将这些数据读取出来，不论是填充到一个树中，或是使用级联显 示出来，需要花费一定的精力。传统的做法，是做一个递归调用，首先连接数据库将顶层数据（也就是parent_xxx为null的记录）读取出来，再对每 一条数据进行递归访问填充集合，这种做法需要连接数据库多次，显然不是较好的解决方法，那么我们能不能通过一次数据库访问，将数据全部读取出来，并且为了 按照父子关系形成集合，使返回的数据满足某种格式。
2. 分析
理想情况下，如果父/子关系数据时严格按照关系结构添加到数据库中，亦即首先添加某条父记录，接着添加该父记录的子记录，如果子记录还包含子记录的 话继续添加，最终数据表中父/子关系按规则排列数据，我们就可以使用某种算法填充集合，但是正如我们所说，这是理想情况，实际情况下数据经常会发生改变， 导致数据没有规律可言，如下图所示，这样的话读取数据填充集合就不太容易的。
所以我们要做的就是通过查询使数据库返回的数据满足这种格式，那么我们的思路是首先查找顶层（0层）记录，再查询第1层记录，接下来是第2层、第3层直到第n层。因为层数是不确定的，所以仍然需要使用递归访问。
SQL Server 2005中提供了新的with关键字，用于指定临时命名的结果集，这些结果集称为公用表表达式（CTE）。该表达式源自简单查询，并且在SELECT、 INSERT、UPDATE或DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。
其语法为：
[ WITH <common_table_expression> [ ,...n ] ] <common_table_expression>::= expression_name[ ( column_name [ ,...n ] ) ] AS ( CTE_query_definition )
使用with关键子的一个简单示例，以下代码将tb_loc表中数据源样输出：
WITH locs(id,name,parent)
AS
(
SELECT * FROM tb_loc
)
SELECT * FROM locs
为了创建良好层次记录结构集，使用with关键字首先读取顶层记录，并且针对每一条顶层记录读取其子记录，直到读取到最底层级记录，最后将所有的记录组合起来，这里用到了UNION ALL关键字，用于将多个查询结果组合到一个结果集中。
接下来就可以使用该关键字创建存储过程返回结果集，并附加每条记录所位于的“层”数，如下图所示：
最后需要在前台界面将其显示出来，由于记录已经按层次返回，需要做的就是按层次首其输出，首先将第0层数据输出，接下来将遍历第0层数据，将第一层 数据添加到合适的父对象中，重复此过程直到填充结果。那么这里的难题就在于如何查找父对象，我们当然可以遍历集合，但是这么做的话如果数据量很大将导致效 率低下。既然可以得到当前对象所位于的层的信息，就也是这树倒置的树是一层一层向下填充的，我们可以定义一个临时集合变量，存储当前层上一层的所有父对 象，在插入当前层对象时遍历集合变量以插入到合适的位置，同时我们还必须保证在逐层读取数据时临时集合变量中持有的始终时当前层上一层所有的对象，程序流 程图如下所示：
根据以上分析，我们就可以编写实现代码了（为了方便，将本文中用到的数据表和创建记录等SQL语句一并给出）。
3. 实现
3.1 打开SQL Server 2005 Management Studio，选择某个数据库输入以下语句创建表结构：
CREATE TABLE [tb_loc](
[id] [int],
[name] [varchar](16),
[parent] [int]
)
GO
3.2 创建测试数据：
INSERT tb_loc(id,name,parent) VALUES( 1,'河北省',NULL) INSERT tb_loc(id,name,parent) VALUES( 2,'石家庄',1) INSERT tb_loc(id,name,parent) VALUES( 3,'保定',1) INSERT tb_loc(id,name,parent) VALUES( 4,'山西省',NULL) INSERT tb_loc(id,name,parent) VALUES( 5,'太原',4) INSERT tb_loc(id,name,parent) VALUES( 6,'新华区',2) INSERT tb_loc(id,name,parent) VALUES( 7,'北焦村',6) INSERT tb_loc(id,name,parent) VALUES( 8,'大郭村',6) INSERT tb_loc(id,name,parent) VALUES( 9,'河南省',NULL) INSERT tb_loc(id,name,parent) VALUES( 10,'大郭村南',8) INSERT tb_loc(id,name,parent) VALUES( 11,'大郭村北',8) INSERT tb_loc(id,name,parent) VALUES( 12,'北焦村东',7) INSERT tb_loc(id,name,parent) VALUES( 13,'北焦村西',7) INSERT tb_loc(id,name,parent) VALUES( 14,'桥东区',3) INSERT tb_loc(id,name,parent) VALUES( 15,'桥西区',3) GO
3.3 创建pr_GetLocations存储过程：
CREATE PROCEDURE pr_GetLocations
AS
BEGIN
WITH locs(id,name,parent,loclevel)
AS
(
SELECT id,name,parent,0 AS loclevel FROM tb_loc
WHERE parent IS NULL
UNION ALL
SELECT l.id,l.name,l.parent,loclevel+1 FROM tb_loc l
INNER JOIN locs p ON l.parent=p.id
)
SELECT * FROM locs
END
3.4 在Visual Studio 2008里创建解决方案并新建一个网站。
3.5 在网站中添加APP_Code目录，并创建Location实体类，该类标识了所在地编号和名称，并且保存了父级所在地编号和它所包含的所有子所在地的集合：
public class Location
{
public int Id
{
get;
set;
}
public string Name
{
get;
set;
}
public LocationCollection SubLocations
{
get;
set;
}
public int ParentId
{
get;
set;
}
public Location()
{
Id = 0;
Name = string.Empty;
SubLocations = new LocationCollection();
ParentId=0;
}
}
3.5 以上代码使用了LocationCollection集合类，使用泛型集合创建该类（同样位于APP_Code目录下）：
using System.Collections.Generic;
public class LocationCollection:List<Location>
{
}
3.6 在APP_Code目录下创建DAO类用于访问数据库，添加必要的命名空间引用：
using System;
using System.Data;
using System.Data.SqlClient;
public class DAO
{
}
3.7编写GetLocations方法，返回所在地集合对象（请根据实际情况修改数据库连接字符串）：
public LocationCollection GetLocations()
{
LocationCollection locs = new LocationCollection();
using (SqlConnection conn = new
SqlConnection("server=.;uid=sa;pwd=00000000;database=temp;"))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "pr_GetLocations";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
int level = 0;
int oldlevel = 1;
LocationCollection container=new LocationCollection();
LocationCollection current = new LocationCollection();
while (reader.Read())
{
Location loc = GetLocationFromReader(reader, out level);
if (level == 0)
{
locs.Add(loc);
container.Add(loc);
}
else
{
if (oldlevel != level)
{
container.Clear();
foreach (Location l in current)
container.Add(l);
current.Clear();
oldlevel = level;
}
current.Add(loc);
CreateLocation(container, loc);
}
}
}
return locs;
}
在该方法按照以下步骤执行：
1. 使用命令对象对象执行pr_GetLocations存储过程返回结果集
2. 如果数据阅读器读取了数据（reader.Read方法返回true）执行：
2.1.从数据阅读器当前记录中读取Location对象，并返回层数信息（out level）
2.2.如果是第一层（level等于0）填充locs集合，并加入到container对象
2.3.如果不是第一层根据层标志（oldlevel）判断当前层是否是新的一层
2.4 如果当前层是新的一层清空container集合并将current集合中实体复制到container集合中，清空current集合并置层标志（oldlevel）
2.5 将当前对象添加到current集合中
2.6 调用CreateLocation方法从container上层集合中匹配当前实体父级对象并加入父对象的子集合中
3. 重复第2步直到读取完全部数据
可以看到container集合始终保存了当前层的上层所有的实体对象，并且为了在更换层数后能够正确的更新container集合，使用current集合保存当前层的实体对象。
3.8 编写GetLocationFromReader方法，用于从数据阅读器中返回Location实体对象，并将层数信息使用out参数返回：
private Location GetLocationFromReader(SqlDataReader reader, out int level)
{
Location loc = new Location();
loc.Id = Convert.ToInt32(reader["id"]);
loc.Name = Convert.ToString(reader["name"]);
object o = reader["parent"];
if (o != DBNull.Value)
loc.ParentId = Convert.ToInt32(o);
level = Convert.ToInt32(reader["loclevel"]);
return loc;
}
3.9 编写CreateLocation方法，该方法遍历实体集合找到与当前实体对象的父级编号匹配的实体，并将当前实体加入到父级实体的子集合中：
private void CreateLocation(LocationCollection container, Location loc)
{
foreach (Location location in container)
{
if (location.Id == loc.ParentId)
{
location.SubLocations.Add(loc);
break;
}
}
}
3.10 向Default.aspx页面上添加TreeView控件：
<asp:TreeView ID="trvLocation" runat="server" Font-Size="12px"
ShowLines="True">
</asp:TreeView>
3.11 在Default.aspx页面后置代码中编写BindData数据绑定方法：
private void BindData()
{
DAO dao = new DAO();
LocationCollection locs = dao.GetLocations();
TreeNodeCollection nodes = CreateTreeNodes(locs);
foreach (TreeNode node in nodes)
{
trvLocation.Nodes.Add(node);
}
}
3.12 BindData方法调用了CreateTreeNode方法返回节点集合，该方法中递归调用自身以得到全部所在地节点：
private TreeNodeCollection CreateTreeNodes(LocationCollection locs)
{
TreeNodeCollection nodeColl = new TreeNodeCollection();
foreach (Location loc in locs)
{
TreeNode node = new TreeNode(loc.Name, loc.Id.ToString());
if (loc.SubLocations.Count > 0)
{
TreeNodeCollection subColl = CreateTreeNodes(loc.SubLocations);
foreach (TreeNode subNode in subColl)
node.ChildNodes.Add(subNode);
}
nodeColl.Add(node);
}
return nodeColl;
}
3.13 最后在页面加载事件里执行数据绑定：
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindData();
}
}
3.14 在浏览器中预览结果：
![clip_image001[6]](https://pic002.cnblogs.com/img/holywolf/200812/2008122208312973.jpg "clip_image001[6]")
4. 总结
原来在处理类似父子关系时总是找不到好的解决办法，现在通过SQL Server 2005里的新特性可以较为合理的解决该类问题，在这里主要用到了with关键字实现递归访问，并且在输出数据时同样使用了递归的方法。如果各位有更好的实现方式，请不不吝赐教。
公用表表达式是Sql Server2005新增加的一个非常好用的功能。
公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。
CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。
与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。
CTE可用于：
1.创建递归查询(我个人认为CTE最好用的地方)
2.在同一语句中多次引用生成的表
CTE优点:
使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。
查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。
CTE可使用的范围：
可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。
下面看一个简单的CTE例题：
把test表中salary最大的id记录保存在test_CTE中,再调用
as
(
select id ,max(salary)
from test
group by id
)
select * from test_cte
由上面例题可以看出：
CTE 由表示 CTE 的表达式名称、可选列列表和定义 CET 的查询组成。
定义 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。
简单的说CTE可以替代临时表和表变量的功能。
我个人认为cte最好用的地方是创建递归查询，下面演示一下这功能：
现有一数据结构如下：
这些数据存放在表Co_ItemNameSet中,表结构和部分数据如下：
ItemId ParentItemId ItemName
2 0 管理费用
3 0 销售费用
4 0 财务费用
5 0 生产成本
35 5 材料
36 5 人工
37 5 制造费用
38 35 原材料
39 35 主要材料
40 35 间辅材料
41 36 工资
42 36 福利
43 36 年奖金
现在需求是:我想查询ItemId=2,也就是管理费用和其下属所有节点的信息
通过CTE可以很简单达到需求要的数据
为了体现CTE的方便性，我特意也写了一个sql2000版本的解决方法，先看看sql2000是怎么解决这个问题的
--sql2000版本
DECLARE @i INT
SELECT @i=2;
/*
使用临时表作为堆栈来跟踪所有正在处理中的项目（已经开始但尚未结束）。
某个项目一旦处理完毕，将被从堆栈中删除。
当发现新的项目时，这些项目将被添加到堆栈中。
*/
CREATE TABLE #tem(
[ItemId] [INT] NOT NULL,
[level] INT
);
/*
存放结果
*/
CREATE TABLE #list(
[ItemId] [INT] NOT NULL,
[ParentItemId] [INT] NOT NULL DEFAULT ((0)),
[ItemName] [nvarchar](100) NOT NULL DEFAULT (''),
[level] INT
);
INSERT INTO #tem([ItemId],[level])
SELECT ItemId, 1
FROM Co_ItemNameSet
WHERE itemid=@i
INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level])
SELECT ItemId, ParentItemId, ItemName ,1
FROM Co_ItemNameSet
WHERE itemid=@i
DECLARE @level INT
SELECT @level=1
DECLARE @current INT
SELECT @current=0
/*
当 @level 大于 0 时，执行以下步骤：
1.如果当前级别 (@level) 的堆栈中有项目，就选择其中一个，并称之为 @current。
2.从堆栈中删除该项目以免重复处理它，然后将其所有子项目添加到堆栈的下一级 (@level + 1) 中。
3.如果有子项目 (IF @@ROWCOUNT > 0)，则下降一级处理它们 (@level = @level + 1)；否则，继续在当前级别上处理。
4.最后，如果在当前级别的堆栈中没有待处理的项目，则返回到上一级，看上一级是否有待处理的项目 (@level = @level - 1)。当再没有上一级时，则完毕。
*/
WHILE(@level>0)
BEGIN
SELECT @current=ItemId
FROM #tem
WHERE [level]=@level
IF @@ROWCOUNT>0
BEGIN
--从堆栈中删除该项目以免重复处理它
DELETE FROM #tem
WHERE [level]=@level and ItemId=@current
--将其所有子项目添加到堆栈的下一级 (@level + 1) 中。
INSERT INTO #tem([ItemId],[level])
SELECT [ItemId],@level+1
FROM Co_ItemNameSet
WHERE ParentItemId=@current
--将其所有子项目添加
INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level])
SELECT [ItemId],[ParentItemId],[ItemName] ,@level+1
FROM Co_ItemNameSet
WHERE ParentItemId=@current
IF @@rowcount>0
BEGIN
SELECT @level=@level+1
END
END
ELSE
BEGIN
SELECT @level=@level-1
END
END
--显示结果
SELECT * FROM #list
DROP TABLE #tem
DROP TABLE #list
go
结果如下：
ItemId ParentItemId ItemName level
2 0 管理费用 1
52 2 汽车费用 2
55 2 招聘费 2
56 2 排污费 2
53 52 燃料 3
54 52 轮胎 3
大家看到sql2000解决这个问题比较麻烦，要实现这需求编写的代码比较多，比较复杂
现在好了，在sql2005中通过CTE的递归特点可以2步就实现.
得到同样的结果，sql2005的CTE代码简单了许多.这就是CTE支持递归查询的魅力。
请看下面的代码：
--sql2005版本
DECLARE @i INT
SELECT @i=2;
WITH Co_ItemNameSet_CTE(ItemId, ParentItemId, ItemName,Level)
AS
(
SELECT ItemId, ParentItemId, ItemName ,1 AS [Level]
FROM Co_ItemNameSet
WHERE itemid=@i
UNION ALL
SELECT c.ItemId, c.ParentItemId, c.ItemName ,[Level] + 1
FROM Co_ItemNameSet c INNER JOIN Co_ItemNameSet_CTE ct
ON c.ParentItemId=ct.ItemId
)
SELECT * FROM Co_ItemNameSet_CTE
go
