如何创建和使用自引用层次表

引言

在 SQL 表中表示组织结构可能很棘手。一种方法是在表中使用自引用键来标识父子关系。

可以引用为

自引用数据结构

ID	RefId	名称
1	null	根
2	1	Customer1
3	1	Customer2
4	1	Customer3
5	2	Dept 1.1
6	2	Dept 1.2
7	2	Dept 1.3
8	3	Dept 2.1
9	4	Dept 3.1
10	4	Dept 3.2

如您所见，RefId 指向每个父级的 ID。 通过此数据结构，我们可以识别父级的所有后代。 例如，ID 4 的子级将是 ID 9 和 10。

本文还将介绍一个表值函数，该函数将为给定的 ID 提供后代 ID 列表。

背景

要设置此项，必须创建并填充单个表。

 CREATE TABLE [dbo].[Organizations](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
    [ParentId] [int] NULL,
 CONSTRAINT [PK_Organizations] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, 
       STATISTICS_NORECOMPUTE = OFF, 
       IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Organizations_ParentId] ON [dbo].[Organizations]
(
    [ParentId] ASC
)WITH (PAD_INDEX = OFF, 
       STATISTICS_NORECOMPUTE = OFF, 
       SORT_IN_TEMPDB = OFF, 
       DROP_EXISTING = OFF, 
       ONLINE = OFF, 
       ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
set identity_insert [dbo].[Organizations] on;

insert into dbo.Organizations ([id], [ParentId], [Name]) values
(1 ,null, 'Root    '),
(2 ,1,'Customer1 '),
(3 ,1,'Customer2 '),
(4 ,1,'Customer3 '),
(5 ,2,'Dept 1.1  '),
(6 ,2,'Dept 1.2  '),
(7 ,2,'Dept 1.3  '),
(8 ,3,'Dept 2.1  '),
(9 ,4,'Dept 3.1  '),
(10 ,4,'Dept 3.2  ')

set identity_insert [dbo].[Organizations] off; 

添加记录时，ParentId 将采用父记录的 Id 值。 所有三个客户的 ParentId 都引用根 ID (1)。 这创建了我们可以用来递归选择子记录的层次结构。

选择子项

用于选择子项的查询非常复杂。 它使用递归（调用自身）来标识父级的每个子项。 此示例使用 CTE（公共表表达式）来简化查询。

;with recur as (
      select a.id,  a.parentid from organizations a
      where a.id=2
      union all 
      select b.id, b.parentid from Organizations b 
      inner join recur c on c.id = b.parentid
    )
    select id from recur

此代码将选择每个从 ID 为 2 的记录派生的记录 ID。

id
-----------
2
5
6
7

这是查询的最简单形式，可以轻松扩展以呈现不仅仅是 ID 的更多值。 但是，它已被缩减以适应下一步 - 创建表值函数。

表值函数

表值函数是返回表的 SQL 函数。 例如，一个名为 dbo.GetDecendants 的表值函数，其输入参数为 id，可以表示为

Select * from dbo.GetDecendants(3)

表值函数设置为将其结果作为表返回。 因此，它可用于返回一组整数（ID），这些整数可用于更复杂的 select 子句中。

select id, parentid, substring(name,1,12) from dbo.Organizations 
       where id in (select * from dbo.GetDecendants(2))

此查询的结果将是

id          parentid    
----------- ----------- ------------
2           1           Customer1 
5           2           Dept 1.1  
6           2           Dept 1.2  
7           2           Dept 1.3  

(4 rows affected)

以下是创建 GetDecendants 函数的代码

create FUNCTION GetDecendants 
(    
    @id int 
)
RETURNS TABLE 
AS
RETURN 
(
    with recur as (
      select a.id,  a.parentid from organizations a
      where a.id=@id
      union all 
      select b.id, b.parentid from Organizations b 
      inner join recur c on c.id = b.parentid
    )
    select id from recur
)
GO 

关注点

这种数据结构对于级联配置值很有用，其中基本配置是在父级设置的，并且每一级后代都为关联的数据对象微调配置。 换句话说，后代的配置值将覆盖为父级设置的值。

这也可以用作索引表，该索引表标识对象的父级和子级，并将详细信息表连接到此 ID。

在该公司，我了解到这种数据结构用于识别医院绩效中的热点。 例如，如果层次结构表示医院、楼层、病房、房间，并且指标应用于房间，则可以使用此模式来汇总医院每个部分的指标。

历史

• 2020 年 1 月 7 日： 初稿