SQL Server 2005新特性之使用with关键字解决递归父子关系

1. 引言
1. 引言

现实项目中经常遇到需要处理递归父子关系的问题，如果把层次关系分开，放在多个表里通过主外键关系联接，最明显的问题就是扩展起来不方便，对于这种情况，一般我们会创建一个使用自连接的表来存放数据。例如存放会员地区数据表结构可能是这样：

 

 

或者某个部分的职员表结构可能如下所示：

 

 

通过类似表结构，我们就可以通过一个表理论上管理无限级数的父/子关系，但是当我们需要将这些数据读取出来，不论是填充到一个树中，或是使用级联显示出来，需要花费一定的精力。传统的做法，是做一个递归调用，首先连接数据库将顶层数据（也就是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

 

 

 

 

select dbo.getstring('110')

create function getstring(@cateid varchar(50))
 RETURNS nvarchar(50)
as
begin
DECLARE @SQL NVARCHAR(4000)
SELECT @SQL=ISNULL(@SQL,'')+'-'+cast(t.name as nvarchar) +'' from (select [name] from category where code in(select * from  dbo.getCategoryID(@cateid))) t
if @SQL!=''
set @SQL=substring(@SQL,2,len(@SQL)+1)
  RETURN @SQL
end

alter function getCategoryID(@cateid varchar(50))
returns @category table(categoryid nvarchar(50))
as
begin
declare @code as varchar(50)
insert into @category values(@cateid)
SELECT @code=subcode FROM category  WHERE  code=@cateid
if @code!=''
begin
While @code>'0'
begin
insert into @category values(@code)
SELECT @code=subcode FROM category  WHERE  code=@code
end
end
return
end