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

目录

介绍

背景

选择孩子

表值函数

兴趣点

---

介绍

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

可以引用为：

自引用数据结构

ID	RefId	Name
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

如您所见，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函数。例如，使用输入参数id 调用的表值函数dbo.GetDecendants可以表示为：

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关联。

在公司，我被介绍了这种数据结构，它被用来识别医院绩效中的热点。例如，如果层次结构表示医院，楼层，机翼，房间，并且将度量应用于房间，则可以使用此模式为医院的每个部分聚合度量。