使用 SQL Server 2005 和 C# 的 TreeToTable 分层表

• 下载源文件 - 3 Kb
• 下载演示项目 - 367 Kb
• 下载 SQL 脚本 - 15 Kb

引言

在 SQL Server 中创建层次表很容易。但当涉及到在 SQL 查询中使用数据时，很明显 SQL 提供的支持有限。本文解决的问题是如何在易于理解的 SQL 语句中使用层次表。我将介绍一个 C# CLR 函数，该函数将层次表从父子结构转换为子父结构。 

该解决方案适用于回答使用层次表时遇到的许多问题。例如，要查看某个组织成员的用户是否也属于另一个组织，假设组织是层次表。我之所以找到这个解决方案，主要是因为报告需求，我需要了解一个组织及其子组织的成员。 

背景

例如，我定义了一个名为 Organization 的表。Organization 有一个 Id 列和一个 ParentId 列，以及一个 Name 列来存储一些无关的示例数据。

假设我们有这个层次结构

 

然后，该表将填充以下记录

ID	ParentId	名称
00000000-0000-0000-0000-00000000000e	00000000-0000-0000-0000-00000000000c	E
00000000-0000-0000-0000-00000000000d	00000000-0000-0000-0000-00000000000c	D
00000000-0000-0000-0000-00000000000c	00000000-0000-0000-0000-00000000000a	C
00000000-0000-0000-0000-00000000000a	NULL	A
00000000-0000-0000-0000-00000000000b	00000000-0000-0000-0000-00000000000a	B

请注意，为了便于阅读，我使用了自定义的唯一标识符，如 '00000000-0000-0000-0000-00000000000a'。在实际应用中，您应该始终使用从您的编程语言生成的唯一标识符。

期望的输出是：

结果记录将是

ID	ChildId
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000a
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000b
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000c
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000d
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000e
00000000-0000-0000-0000-00000000000b	00000000-0000-0000-0000-00000000000b
00000000-0000-0000-0000-00000000000c	00000000-0000-0000-0000-00000000000c
00000000-0000-0000-0000-00000000000c	00000000-0000-0000-0000-00000000000d
00000000-0000-0000-0000-00000000000c	00000000-0000-0000-0000-00000000000e
00000000-0000-0000-0000-00000000000d	00000000-0000-0000-0000-00000000000d
00000000-0000-0000-0000-00000000000e	00000000-0000-0000-0000-00000000000e

实际上发生的是将项-父关系转换为项-子关系。

使用代码

此转换由名为 TreeToTable 的函数执行，例如： 

SELECT * FROM TreeToTable('Organization', 'Id', 'ParentId', NULL)

参数是

TableName	层次表的名称。
IdColumn	ID 列的名称。
ParentIdColumn	父 ID 列的名称。
RootId	当您想从某个点查看树时用作根的 ID；如果提供 NULL，则使用父 ID 为 NULL 的记录，这通常是层次结构中的根，但这并不是根记录本身的 ID！

由于有时只查看特定级别的记录很有用，例如，如果您只想获取根级别（不包括子组织）的所有组织。我向输出添加了一个 Dept 列。我还添加了一个 ChildDepth 列。因此，实际输出看起来像

ID	ChildId	深度	ChildDepth
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000a	0	0
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000b	0	1
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000c	0	1
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000d	0	2
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000e	0	2
00000000-0000-0000-0000-00000000000b	00000000-0000-0000-0000-00000000000b	1	1
00000000-0000-0000-0000-00000000000c	00000000-0000-0000-0000-00000000000c	1	1
00000000-0000-0000-0000-00000000000c	00000000-0000-0000-0000-00000000000d	1	2
00000000-0000-0000-0000-00000000000c	00000000-0000-0000-0000-00000000000e	1	2
00000000-0000-0000-0000-00000000000d	00000000-0000-0000-0000-00000000000d	2	2
00000000-0000-0000-0000-00000000000e	00000000-0000-0000-0000-00000000000e	2	2

现在，如果您只想获取根级别或根级别以下的组织，您的查询可能如下所示：

SELECT * FROM TreeToTable('Organization', 'Id', 'ParentId', NULL) 
WHERE Depth = 0

输出将是：

ID	ChildId	深度	ChildDepth
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000a	0	0
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000b	0	1
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000c	0	1
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000d	0	2
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000e	0	2

如果您想从结果中省略组织本身，并且只想要根组织，您的查询可能如下所示：

SELECT * FROM TreeToTable('Organization', 'Id', 'ParentId', NULL) 
WHERE Depth = 0 AND Depth != ChildDepth

输出将是：

ID	ChildId	深度	ChildDepth
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000b	0	1
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000c	0	1
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000d	0	2
00000000-0000-0000-0000-00000000000a	00000000-0000-0000-0000-00000000000e	0	2

如果您想将组织名称返回，您的查询可能如下所示：

SELECT ORG.*, Organization.Name
FROM TreeToTable('Organization', 'Id', 'ParentId', NULL) ORG
INNER JOIN Organization ON Organization.Id = ORG.Id

现在您可能想与其他表进行连接以检索有用的数据。典型的连接如下所示，假设您有一个名为 Person 的表，该表有一个引用 Organization 表的列，名为 OrganizationId （请注意，由于示例数据库中没有 Person 表，此代码将无法工作）。

SELECT * 
FROM TreeToTable('Organization', 'Id', 'ParentId', '', NULL) ORG
INNER JOIN Person ON Person.OrganizationId = ORG.Id

此输出现在包含出现在每个组织（包括任何子组织）中的所有 Person。此输出可用于报告中的分组。

示例代码包含 C# CLR 函数和一个可以生成示例数据的小型应用程序。包含了一个即用型 SQL Server Express 数据库，尽管 TreeToTable() 适用于任何版本的 SQL Server 2005。建议将解决方案放在 C:\ 目录下。

解释代码

代码量太大，无法完全解释。我希望我的注释能帮助您了解细节。关于在 SQL Server 中编写 C# 的一般信息随处可见，您可能想尝试 CLR 用户定义函数。

您可能还想阅读 使用 CLR 编写数据库对象，以获取有关如何使用 Visual Studio 编写和部署 C# SQL 函数的更多信息。

TreeToTable 函数声明如下：

[SqlFunction(DataAccess = DataAccessKind.Read, 
 FillRowMethodName = "TreeToTableFillRow",
 TableDefinition = 
  "Id uniqueidentifier, ChildId uniqueidentifier, Depth int, ChildDepth int")
]
public static IEnumerable TreeToTable
(string tableName, 
 string idColumnName, 
 string parentIdColumnName, 
 SqlGuid rootId)
{
    // ...
}

属性中的 FillRowMethodName="TreeToTableFillRow" 是必需的，并且映射到此方法：

public static void TreeToTableFillRow(Object obj, out SqlGuid id, 
    out SqlGuid childId, out int depth, out int childDepth) 
{
   // ... 
}

TreeToTableFillRow(...) 方法会针对从 TreeToTable(...) 枚举器返回的每个对象进行调用。这基本上是 CLR 表值函数的工作方式，这在许多文章中都有介绍，我在这里就不再解释了。

因此，这是我对 TreetoTable(...): 实现的简要描述：

首先，在当前进程的上下文中建立连接：

SqlConnection cn = new SqlConnection("Context Connection=true");

接下来，构建一个 SQL SELECT 语句：

string sql = string.Format("SELECT {0}, {1} FROM {2} ", idColumnName, 
                           parentIdColumnName, tableName);

例如，这将求值为：SELECT Id, ParentId FROM Organization

然后，打开一个 SqlDataReader 。SqlDataReader 读取所有记录，并为每条记录创建一个 Node 对象。在 node 对象中，存储 Id 和 ParentId 的值。此 node 对象存储在名为 source 的 Dictionary 中。此外，root 现在也已确定，因为无需额外的循环来查找根。

using (SqlDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        SqlGuid id = rdr.GetSqlGuid(0);
        SqlGuid parentId = rdr.GetSqlGuid(1);
        Node node = new Node(id, parentId);
        source.Add(id, node);
        // If the requested root is null, the node with parent-id null is 
        // used
        if (id == rootId || rootId.IsNull && parentId.IsNull)
        {
            root = node;
        }
    }
    rdr.Close();
}

它按 id 存储在 Dictionary 中，因为我们在构建节点层次结构时需要大量基于 id 的搜索。

// Create hierarchy
foreach (Node node in source.Values)
{
    Node parent;
    if (source.TryGetValue(node.ParentId, out parent))
    {
        parent.Add(node);
    }
}

接下来，通过调用递归方法 CalculateDepth(...) 来确定节点的 Depth ，从 root 开始。此方法仅将每个子节点的 Depth 属性设置为比其父节点多 1。

// Calculate the child-depth
root.CalculateDepth(0);

现在我们有了一个节点层次结构，只需要将其“展平”就可以返回结果。

// Flatten
RowList result = new RowList();
root.Flatten(result, root.Id, root.Depth, maxDepth);
foreach (Node node in root.SubNodeList())
{
    node.Flatten(result, node.Id, node.Depth, maxDepth);
}

展平是 node 对象上的一个方法。它将自身及其所有子项的 Row 对象附加到 result 对象，而 result 只是一个类型为 Row 的 List 。

public void Flatten(RowList result, SqlGuid id, int depth, int maxDepth)
{
    if (depth > maxDepth)
    {
        return;
    }

    Row row = new Row(id, this.Id, depth, this.Depth);
    result.Add(row);

    foreach (Node node in this.SubNodeList())
    {
        Row row2 = new Row(id, node.Id, depth, node.Depth);
        result.Add(row2);
    }
}

这就是全部，result 现在是 id's、parentId's、depth's 和 childDepth's 的完整列表。此列表通过 TreeToTableFillRow(...) 方法发送到 T-SQL，由 SqlServer 处理。

性能

我认为 TreeToTable 函数的性能相当不错。它每秒处理 20,000 条记录，输出近 130,000 行，在我的 AMD Sempron 2800 笔记本上进行了测试。处理时间与输入记录数成线性关系，这让我有点惊讶，因为涉及到大量的递归和搜索。

该函数可能很快，但使用时应小心。SQL Server 无法像处理 T-SQL 那样应用任何查询优化，结果也不能缓存。

可以通过添加 SQL WHERE 子句参数来提高性能，这样它就不必处理那么多输入记录。通过添加最大深度参数也可以提高性能，这样它就不会输出那么多您可能不使用的行。我尝试了这两种选项，它们在特定场景下确实能加快速度。我认为少量的参数使函数更容易使用，因此我没有将其包含在可下载的代码中。使用 SQL 表达式可以达到相同的结果，并且查询也更易于阅读。

为了测试唯一标识符与 int 类型的对比，我还创建了一个 int 版本。令人惊讶的是，int 版本运行得更快。请参见下图。

 

创建测试数据

为了创建测试数据，我在下载文件中包含了一个小型 Windows 应用程序。

 

关注点

还有其他方法可以实现相同的结果。带游标的存储过程可以产生您想要的任何内容，尽管我觉得它们很难编写，而且它们通常只解决非常具体的问题。SQL Server 2005 中非常流行的自定义表表达式（CTE）承诺速度非常快且易于使用，尽管我未能返回相同的结果，而且我想知道它们是否会同样快。我对替代方案感兴趣，请发给我。

历史

此第一个版本提交于 2006 年 8 月 12 日。