《SQL WITH AS实现父子递归查询.doc》由会员分享，可在线阅读，更多相关《SQL WITH AS实现父子递归查询.doc（13页珍藏版）》请在金锄头文库上搜索。
1、SQL Server 2005新特性之使用with关键字解决递归父子关系 1. 引言 现实项目中经常遇到需要处理递归父子关系的问题，如果把层次关系分开，放在多个表里通过主外键关系联接，最明显的问题就是扩展起来不方便，对于这种情况，一般我们会创建一个使用自连接的表来存放数据。例如存放会员地区数据表结构可能是这样： 列名描述location_id地区编号location_name地区名称parentlocation_id上级地区编号或者某个部分的职员表结构可能如下所示： 列名描述employee_id职员编号employee_name职员名称manager_id职员的直接上级管理者,和employ
2、ee_id进行自联接通过类似表结构，我们就可以通过一个表理论上管理无限级数的父/子关系，但是当我们需要将这些数据读取出来，不论是填充到一个树中，或是使用级联显示出来，需要花费一定的精力。传统的做法，是做一个递归调用，首先连接数据库将顶层数据（也就是parent_xxx为null的记录）读取出来，再对每一条数据进行递归访问填充集合，这种做法需要连接数据库多次，显然不是较好的解决方法，那么我们能不能通过一次数据库访问，将数据全部读取出来，并且为了按照父子关系形成集合，使返回的数据满足某种格式。 2. 分析 理想情况下，如果父/子关系数据时严格按照关系结构添加到数据库中，亦即首先添加某条父记录，接着
3、添加该父记录的子记录，如果子记录还包含子记录的话继续添加，最终数据表中父/子关系按规则排列数据，我们就可以使用某种算法填充集合，但是正如我们所说，这是理想情况，实际情况下数据经常会发生改变，导致数据没有规律可言，如下图所示，这样的话读取数据填充集合就不太容易的。 所以我们要做的就是通过查询使数据库返回的数据满足这种格式，那么我们的思路是首先查找顶层（0层）记录，再查询第1层记录，接下来是第2层、第3层直到第n层。因为层数是不确定的，所以仍然需要使用递归访问。 SQL Server 2005中提供了新的with关键字，用于指定临时命名的结果集，这些结果集称为公用表表达式（CTE）。该表达式源自简
4、单查询，并且在SELECT、INSERT、UPDATE或DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。 其语法为： WITH ,.n :=expression_name ( column_name ,.n ) AS( CTE_query_definition )使用with关键子的一个简单示例，以下代码将tb_loc表中数据源样输出：WITH locs(id,name,parent)AS( SELECT * FROM tb_loc)SELECT
5、* FROM locs为了创建良好层次记录结构集，使用with关键字首先读取顶层记录，并且针对每一条顶层记录读取其子记录，直到读取到最底层级记录，最后将所有的记录组合起来，这里用到了UNION ALL关键字，用于将多个查询结果组合到一个结果集中。 接下来就可以使用该关键字创建存储过程返回结果集，并附加每条记录所位于的“层”数，如下图所示： 最后需要在前台界面将其显示出来，由于记录已经按层次返回，需要做的就是按层次首其输出，首先将第0层数据输出，接下来将遍历第0层数据，将第一层数据添加到合适的父对象中，重复此过程直到填充结果。那么这里的难题就在于如何查找父对象，我们当然可以遍历集合，但是这么做的
6、话如果数据量很大将导致效率低下。既然可以得到当前对象所位于的层的信息，就也是这树倒置的树是一层一层向下填充的，我们可以定义一个临时集合变量，存储当前层上一层的所有父对象，在插入当前层对象时遍历集合变量以插入到合适的位置，同时我们还必须保证在逐层读取数据时临时集合变量中持有的始终时当前层上一层所有的对象，程序流程图如下所示： 根据以上分析，我们就可以编写实现代码了（为了方便，将本文中用到的数据表和创建记录等SQL语句一并给出）。 3. 实现 3.1 打开SQL Server 2005 Management Studio，选择某个数据库输入以下语句创建表结构： CREATE TABLE tb_lo
7、c( id int, name varchar(16), parent int)GO3.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 t
8、b_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,na
9、me,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) GO3.3 创建pr_GetLocations存储过程：CREATE PROCEDURE pr_GetLocationsASBEGIN WITH locs(id,name,parent,loclevel) AS ( SELECT id,name,pa
10、rent,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 locsEND3.4 在Visual Studio 2008里创建解决方案并新建一个网站。 3.5 在网站中添加APP_Code目录，并创建Location实体类，该类标识了所在地编号和名称，并且保存了父级所在地编号和它所包含的所有子所在地的集合：public cla
11、ss 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
12、目录下）：using System.Collections.Generic;public class LocationCollection:List 3.6 在APP_Code目录下创建DAO类用于访问数据库，添加必要的命名空间引用：using System;using System.Data;using System.Data.SqlClient;public class DAO3.7编写GetLocations方法，返回所在地集合对象（请根据实际情况修改数据库连接字符串）：public LocationCollection GetLocations() LocationCollection
13、 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)
