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  

9 7 3 1 2 3 4 5 6 7 4 8 :