关系数据库中对于层次结构数据的处理分析

摘要：在数据库应用系统开发中，经常要对层次结构数据进行管理，实现的方法有多种，比较分析了每种方法的优缺点。利用Microsoft SQL Server 2005中的新功能公用表表达式（CTE，Common Table Expression）进行递归查询，结合组织机构管理的具体实例，给出了使用CTE进行递归查询的组织机构管理的通用实现方法。

关键词：关系数据库；层次数据；递归查询；公用表表达式；组织机构管理

中图分类号：TP311文献标识码：A文章编号：1009-3044(2011)28-6821-04

Analysis of how to Manage Hierarchical Structure Data in Relational Database

ZHAGN Shen-yong1, WU Fang1, LIAO Ji-yong2, XIAO You-qing1, LE Xiao-yan1

(1.School of Computing, Beijing Institute of Technology, Zhuhai 519085, China; 2.Anhui Telecom Planning and Designing CO.LTD, Hefei 230031, China)

Abstract: In develop of database application system, often manages the hierarchical structure data, there are many methods, analyses the advantages and disadvantages of each method by comparing. Implements recursive query with the common table expression the new function of Microsoft SQL Server 2005, on the example of organizations management, provides the general way of how to manage organizations through recursive query using CTE.

Key words: relational database; hierarchical structure data; recursive query; common table expression (CTE); organizations management

1绪论

在数据库应用系统开发中，经常要对层次结构的数据对象进行管理。例如,产品的类别和分类、组织机构、行政辖区和人事管理等等。现阶段，主流的数据库管理系统都是基于关系模型的关系数据库管理系统。如何对这些具有层次结构的数据进行建表和管理，是一个必须要解决的实际问题。通过比较，分析了两种建表方式的优缺点；结合组织机构管理的实例，利用CTE进行递归查询，给出一种通用的解决方案。

2 层次模型和关系模型的比较

数据库管理系统总是基于一定的数据模型的，目前，数据库领域常用的逻辑数据模型有：层次模型、网状模型、关系模型、面向对象模型和对象关系模型。层次模型和网状模型统称为格式化模型，该模型的数据库管理系统在20世纪70年代至80年代初非常流行，在数据库管理系统中占据主导地位，现在已经逐渐被关系模型的数据库管理系统所取代[1]。20世纪80年代以来，面向对象的方法和技术在计算机领域的应用和发展，也促使了面向对象数据模型的研究和发展，但是，现阶段主流的数据库管理系统都是基于关系模型。

2.1 层次模型

层次模型是数据库管理系统中最早出现的数据模型，层次数据库管理系统采用层次模型作为数据的组织方式。层次模型用树形结构来表示各类实体以及实体之间的联系，现实世界中许多实体之间的联系本身就是一种自然的层次关系，如行政机构、家族关系等。图1为层次模型的示例图，可以看出层次模型像一棵倒立的树，结点的双亲是唯一的。

层次模型的特点是，任何一个给定的记录值只有按其路径查看时，才能显示出它的全部意义，没有一个子女记录值能够脱离双亲记录值而独立存在。

层次模型的优点是：1）数据结构比较简单；2）层次数据库的查询效率高；3）层次数据模型提供了良好的数据完整性支持；

缺点主要是: 1）查询子女结点必须通过双亲结点；2）由于结构严密，数据的存取操作是在真正的物理层次上进行操作，随着应用环境的扩大，程序开发的代码非常复杂，不容易掌握[2]。

但是它对于具有一对多的层次联系的组织机构的描述非常自然、直观、容易理解。

2.2 关系模型

关系模型是当今最为重要的一种数据模型，关系数据库系统采用关系模型作为数据的组织方式。从用户角度看，关系模型由一组关系组成，每个关系的数据结构就是一张二维表，图2为关系模型的数据结构。与层次模型不同，关系模型是建立在严格的数学概念的基础上的，它的理论基础就是关系代数。在关系模型中，实体和实体之间的联系都用二维表来表示。

关系模型的优点：1）关系模型与格式化模型不同，它有严格的数学理论基础；2）关系模型概念单一，无论是实体还是实体之间的联系都用关系来表示；对数据的检索和更新结果也是关系，所以数据结构简单、清晰，用户易懂易用。3）数据的存取路径对用户透明，简化了程序员的工作和数据库开发工作。所以，关系数据模型自诞生以来，发展迅速，现在主流的数据库系统都是基于关系模型的。

3关系模型中对于层次结构数据的处理

关系数据库管理系统因为其自身的优点，成了现在主流的数据库系统，但是，如何用关系数据库来管理具有层次结构的数据是一个现实的问题。例如企业的组织机构，就是一个典型的层次结构数据。图3为XX集团的组织机构图。关系数据库系统中，对于这种组织机构数据如何建立二维表进行数据管理，有两种不同的解决方法，一种是分级建表，另一种是集中建立。

对于上图中的组织机构，使用分级建表的方式，就是每一级部门建立一个表。如果把集团总公司作为第一级部分，那么服装公司、贸易公司、食品公司就是二级部分，下面的生产部、采购部等就是三级部门，依次类推，最下面的广州办事处等就是5级部门，分别建立下面5个表，表结构分别如表1―表5所示。

表2 二级部门信息表（tbl_SecondDeptInfo）数据字典 表3 三级部门信息表（tbl_ThirdDeptInfo）数据字典

■

表4四级部门信息表（tbl_FourthDeptInfo）数据字典 表5 五级部门信息表（tbl_FifthDeptInfo）数据字典

■

在上面这5个表中，除了第一个表没有外键，其他四个表都是上一个表的子表，建立了主外键引用关系。而且后面四个表的结构相似。这种分级建表的方式，优点是表结构清晰，直接显示了部门所属的级别，对于数据的增、删、改、查都比较方便，如果要修改某个级别的部门，直接修改该级别的部门表，如果要查询部门信息以及上级或者下级部门的信息，只需要根据外键做相应的连接查询。但是这种分级建表的方式，最大的缺点是可扩展性差，在建表之前，必须确定该组织机构一共有多少个级别，以此来确定应该建几个表，所以，如果将来部门的分级增加了，系统将不能适应这个变化，因而系统的可扩展性很差。在实际应用中，往往对组织机构的分级会越来越细，为了能够适应这种变化，应该考虑使用另外一种方式建表，即集中式建表，对于组织机构只建立一个表，在这个表里反映出部门之间的上下级隶属关系。表结构如表6 所示。

部门信息表，通过定义自参照外键，实现部门之间的隶属关系。对于上面的一级部门，由于不存在上级部门了，所以它的上级部门编号就是空值（NULL）。这种建表方式的最大优点是可扩展性好，将来不论组织机构的级别是增加还是减少，都不需要增加新的表，也不需要修改表的结构。缺点是要想查询某个部门的上级或者下级部门信息以及确定部门所属的级别，往往要通过递归查询，在Micros SQL Server 2005以前版本的数据库管理系统中，一般要通过创建临时表、游标或者视图来实现这种递归查询，而且查询的语句比较复杂，编程相对比较困难[3]；在SQL Server 2005版中由于采用了公用表表达式（common table expression，CTE）技术，使得实现递归查询的查询语句变得非常简单。

4公用表表达式（CTE）

微软公司在SQL Server 2005中引入了公用表表达式技术。CTE可以认为是在单个 SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句的执行范围内定义的临时结果集。CTE与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE可以自引用，也可以在同一查询中引用多次[4]。

在CTE中可以包括对自身的引用，从而创建递归CTE。递归CTE是一个重复执行初始 CTE以返回数据子集直到获取完整结果集的公用表表达式。当某个查询引用递归CTE时，它即被称为递归查询，递归查询通常用于返回分层数据。递归CTE可以极大地简化在 SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句中运行递归查询所需的代码[5]。

4.1 CTE的结构

CTE由表示CTE的表达式名称、可选列列表和定义CTE的查询组成。定义CTE后，可以在SELECT、INSERT、UPDATE或DELETE语句中对其进行引用，就像引用表或视图一样。CTE也可用于CREATE VIEW语句，作为定义SELECT语句的一部分。

CTE的基本语法结构如下：

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

只有在查询定义CTE_query_definition中为所有结果列都提供了不同的名称时，列名称列表[ ( column_name [,...n] ) ]才是可选的。

调用CTE的语句为：SELECTFROMexpression_name;

4.2 CTE的递归查询

Transact-SQL中的递归CTE的结构与其他编程语言中的递归程序相似。其他语言中的递归程序可能返回标量值，但递归CTE可以返回多行结果集。

递归CTE由下列三个元素组成：

1）程序的调用

递归CTE的第一个调用包括一个或多个由UNION ALL、UNION、EXCEPT或INTERSECT运算符联接的CTE_query_definitions。由于这些查询定义形成了CTE结构的基准结果集，所以它们被称为“定位点成员”。

CTE_query_definitions被视为定位点成员，除非它们引用了CTE本身，所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用UNION ALL运算符联接最后一个定位点成员和第一个递归成员。

2）程序的递归调用

递归调用包括一个或多个由引用CTE本身的UNION ALL运算符联接的CTE_query_definitions，这些查询定义被称为“递归成员”。

3）终止检查

终止检查是隐式的；当上一个调用中未返回行时，递归将停止。

递归CTE的基本语法结构如下：

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition --定位点的定义

UNION ALL

CTE_query_definition --调用了cte_name的递归查询。

下面使用CTE，演示对上面组织机构管理的具体实现。

5 具体案例

对于上图3.所示的组织机构，使用集中建表的方式，根据表6.所示的数据字典，使用如下的代码创建部门信息表（tbl_DepartmentInfo）。

Create Table tbl_DepartmentInfo

(DeptIDVarChar(12) primary key,

DeptNameNvarchar(50) not null,

ParDIDVarChar(12)referencestbl_DepartmentInfo(DeptID),

DispOrderVarchar(2),

MemoryNvarchar(50),

Unique(DeptName,ParDID));

其中上级部门编号（PraDID）是外键，参照了本表的部门编号（DeptID）列，部门名称（DeptName）和上级部门编号（ParDID）满足唯一键约束，即假设不同部门的下级部门名称可以相同，但是同一个部门的下级部门名称是不能相同的。对于部门编号（DeptID），在实际的应用系统开发中，可以使用自定义函数产生部门编号，也可以使用uniqueidentifier数据类型定义该列，使用NEWID函数产生相应的编号；因此，这些编号一般只作为主键的一个唯一标识，不需要实际的意义，一般也不会显示在应用程序的界面上，记录可以通过应用系统进行录入，录入时可以在应用程序的界面上选择相应的上一级部门。为了能够按一定的顺序显示部门信息，因而设置了显示顺序（DispOrder）列来控制部门信息显示的先后，例如，可以通过设置字母A-Z来标识同一上级部门的下级部门的显示顺序。

向表里插入图3.中的所有部门的信息，为了便于演示，直接指定了部门编号。插入数据的部分Insert语句如下所示。

Insert into tbl_DepartmentInfo values('1J001','XX集团总公司', NULL, 'A',NULL);

Insert into tbl_DepartmentInfo values('2J001','XX服装公司','1J001', 'A',NULL);

Insert into tbl_DepartmentInfo values('3J002','生产部', '2J001', 'B',NULL);

Insert into tbl_DepartmentInfo values('4J003','华东分部', '3J003', 'A',NULL);

Insert into tbl_DepartmentInfo values('5J003','武汉办事处', '4J005', 'A',NULL);

插入数据后，表里的记录如图4所示。

对于上表中的记录，很难直观的观察到部门之间的隶属关系，也不能确定部门所处的级别。所以，在应用系统开发时，希望能分级显示部门信息，而且直观显示每个部门所处的级别，这样才能根据用户的要求显示相关部门的信息。为了能直观显示部门之间的隶属关系，以及每个部门所处的级别，要对部门信息表（tbl_DepartmentInfo）进行递归查询，使用CTE，创建一个可以直观显示组织机构层次关系的视图（vi_Dept）。定义视图的代码如下。

CreateViewvi_Dept

as

With d_CTE(DepartmentID, ParentDepartmentID,部门名称,上级部门名称, 部门级别,显示顺序,备注)AS

(SELECT DeptID, convert(varchar(max),ParDID) as ParDID, DeptName, convert(nvarchar(max), NULL) as ParDName, 0 AS DeptLevel, convert(varchar(max), DispOrder), MemoryFROM tbl_DepartmentInfoWHEREParDIDISNULL--定义定位成员

UNION ALL

SELECTd.DeptID, case when cte.ParentDepartmentID IS NULLthen d.ParDIDelse cte.ParentDepartmentID+'/'+d.ParDID end, d.DeptName, case when cte.上级部门名称 IS NULL then cte.部门名称 else cte.上级部门名称+ '/' + cte.部门名称 end, 部门级别+ 1, cte.显示顺序+ d.DispOrder, d.memory FROM tbl_DepartmentInfo d INNER JOIN d_CTE cte ON d.ParDID = cte.DepartmentID ) --定义引用了d_CTE自身的递归成员

SELECT DepartmentID 部门编号, ParentDepartmentID 上级部门编号, 部门名称, 上级部门名称, 部门级别, 显示顺序, 备注 FROMd_CTE

在上面创建视图的代码里，首先，创建了一个公用表表达式d_CTE，它有DepartmentID、 ParentDepartmentID、部门名称、上级部门名称、部门级别、显示顺序、备注这七列构成，第一个Select语句定义了定位点成员，把tbl_DepartmentInfo表中上级部门编号为空值（NULL）作为递归执行的结束，同时指定该部门的级别为0。第二个Select语句定义了递归成员，它把tbl_DepartmentInfo表和公用表表达式d_CTE进行内连接，连接的条件是tbl_DepartmentInfo表里的上级部门编号等于d_CTE的部门编号，每执行一次连接就把部门级别在上个部门级别的基础上加1，并且把每个部门的上级部门编号和名称跟上上一级部门编号和名称分别用“/”联接起来，显示顺序也进行了联接。连接后的结果集又和tbl_DepartmentInfo表进行连接，如此循环下去，直到没有满足连接条件的结果集了，递归将停止，通过UNION ALL运算，把满足连接条件的结果集和定位点的结果集联接起来，由于UNION ALL运算要求列的数据结构要完全一样，包括数据类型、长度与精确，因此在代码中使用convert(varchar(max),ParDID)将上级部门编号等列转换成varchar(max)数据类型。最后，根据d_CTE创建了视图vi_Dept。

执行查询“select*fromvi_Deptorderby 显示顺序”，结果集内容如图5所示。

对于上面通过视图vi_Dept查询到的结果，可以直观看出各部门之间的层次关系，也能确定部门所在的级别，上面图5的记录内容在应用程序界面中可以用TreeView等控件显示出来。通过查询视图vi_Dept，可以直接把某一级别的所有部门列出，也可以查询某一个部门以及它的所有下级部门的信息。例如“select * from vi_Dept where 部门编号='3J003' or 上级部门编号 like '%3J003%' order by 显示顺序”，查询的结果集如图6所示。所以，根据视图vi_Dept，可以很容易得到用户应用程序界面中需要显示的各种信息。

6 总结

虽然现在主流的数据库管理系统都是基于关系模型的，但是经常要处理有层次结构的数据对象。通过建立一张具有自参照完整性的表，利用Microsoft SQL Server 2005中的公用表表达式的递归查询功能，生成一个能够呈现对象之间的层次关系的视图，可以满足应用系统开发中的各种查询需要；不但简化了编程工作，提高项目开发效率；而且提高了系统的可扩展性，改善程序的性能。案例中具体实现的SQL脚本，对于层次结构数据的管理具有通用性，可以为同类型的应用提供参考。

参考文献：

[1] 王珊,萨师煊.数据库系统概论[M].4版.北京:高等教育出版社,2006:18-30.

[2] Jeffrey D Ullman,Jennifer Widom.A First Course in Database System(Third Edition)[M].北京:机械工业出版社,2008:17-24.

[3] 刘进. 浅谈T-SQL 语言之递归查询[J].沈阳师范大学学报:自然科学版, 2007,25(2):217-219.

[4] MSDN 使用公用表表达式[EB/OL]. /zh-cn/library/ms190766.aspx.

[5] MSDN 使用公用表表达式的递归查询[EB/OL]./zh-cn/library/ms186243.aspx.