帮酷LOGO
0 0 评论
文章标签:tables    tab    Hiera  

介绍

在 SQL Server 中创建分层的table 很容易。 但是,在使用SQL查询中的数据时,很明显,SQL提供了有限的支持。 本文解决的问题是如何在易于理解的sql语句中使用层次表。 在 C#的函数中,将层次结构从项目级结构转换为项目子结构。

这个解决方案适合于回答使用分层 table 所涉及的许多问题。 例如,如果是组织成员的一个用户也是它的他组织,假定组织是层次结构的table。 导致这个解决方案的主要原因是报告,我想知道一个组织的成员和它的子组织。

背景

作为一个例子,我定义了一个名为组织的table。 组织的标识为 column column column column列,以存储一些无关的示例数据。

假设我们有这个层次:

Sample input data

然后,table 将填充以下记录:

Id
ParentId
姓名
00000000-0000-0000-0000-00000000000 英镑00000000-0000-0000-0000-00000000000镑英镑E
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000镑英镑D
00000000-0000-0000-0000-00000000000镑英镑00000000-0000-0000-0000-00000000000英镑C
00000000-0000-0000-0000-00000000000英镑A
00000000-0000-0000-0000-00000000000 英镑00000000-0000-0000-0000-00000000000英镑B

注意,我使用了 home uniqueidentifiers,比如'。00000000英镑 '。 在现实世界中,你应该始终使用从编程语言生成的uniqueidentifiers。

所需的输出为:

Sample output schema

生成的记录将为:

Id
ChildId
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000英镑
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000 英镑
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000镑英镑
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000英镑
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000 英镑
00000000-0000-0000-0000-00000000000 英镑00000000-0000-0000-0000-00000000000 英镑
00000000-0000-0000-0000-00000000000镑英镑00000000-0000-0000-0000-00000000000镑英镑
00000000-0000-0000-0000-00000000000镑英镑00000000-0000-0000-0000-00000000000英镑
00000000-0000-0000-0000-00000000000镑英镑00000000-0000-0000-0000-00000000000 英镑
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000英镑
00000000-0000-0000-0000-00000000000 英镑00000000-0000-0000-0000-00000000000 英镑

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

使用代码

转换由名为TreeToTable的函数执行,例如:

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

参数是:

TableName分层表的NAME。
IdColumnid列的NAME。
ParentIdColumnparent-id-column的NAME。
RootId如果你想从某一点查看树,则用作 root的Id,如果你提供的是空的父标识,那么该记录通常为空,但是这不是根记录本身的标识 !

如果只是想要获得 root 级别的所有组织,而不希望从所有组织中获得组织。 我在输出中添加了一个 Dept 列。 我还在输出中添加了一个 ChildDepth 列。 因此,真正的输出看起来像:

Id
ChildId
深度
ChildDepth
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000英镑00
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000 英镑01
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000镑英镑01
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000英镑02
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000 英镑02
00000000-0000-0000-0000-00000000000 英镑00000000-0000-0000-0000-00000000000 英镑11
00000000-0000-0000-0000-00000000000镑英镑00000000-0000-0000-0000-00000000000镑英镑11
00000000-0000-0000-0000-00000000000镑英镑00000000-0000-0000-0000-00000000000英镑12
00000000-0000-0000-0000-00000000000镑英镑00000000-0000-0000-0000-00000000000 英镑12
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000英镑22
00000000-0000-0000-0000-00000000000 英镑00000000-0000-0000-0000-00000000000 英镑22

如果现在只想获取 root 级别或者下面的组织,查询可能会如下所示:

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

输出将为:

Id
ChildId
深度
ChildDepth
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000英镑00
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000 英镑01
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000镑英镑01
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000英镑02
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000 英镑02

如果希望从结果中删除组织本身,并且只需要组织,则你的查询可能类似于:

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

输出将为:

Id
ChildId
深度
ChildDepth
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000 英镑01
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000镑英镑01
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000英镑02
00000000-0000-0000-0000-00000000000英镑00000000-0000-0000-0000-00000000000 英镑02

如果要使组织的NAME 返回,你的查询可能如下所示:

SELECT ORG.*, Organization.NameFROM TreeToTable('Organization', 'Id', 'ParentId', NULL) ORGINNERJOIN Organization ON Organization.Id = ORG.Id

现在,你可能需要与其他表进行 Join,以便检索有用的数据。 一个典型的Join 看起来像这样,假设有一个名为:的table 名为 OrganizationId ( 注意,这里代码不能工作,因为示例数据库中没有 Person table )的Organization table。

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

这里输出现在包含每个组织中出现的所有人员,它的中包括任何子组织。 这里输出可以在执行分组的报表中使用。

示例代码包含 C# CLR函数和一个可以生成示例数据的小应用程序。 准备使用 SQL Server Express 数据库,尽管 TreeToTable() 将在任何版本的SQL Server 2005上工作。 建议将解决方案放在 C:. 处。

解释代码

代码太多,不能完全解释。 我希望我的评论能帮助你在细节方面有所帮助。 一般情况下,关于 C# 编程的一般信息被广泛使用,你可能需要尝试用户定义的函数。

在CLR中,你可能希望读取数据库对象for关于如何使用编写和部署 C# SQL函数的更多信息。

TreeToTable函数声明为:

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

iPhone 7 还没出来,我们已经在iPhone上获取细节 8,或者不管是想到下一步。 FillRowMethodName="TreeToTableFillRow" 在需要的属性中,并映射到这里方法:

publicstaticvoid TreeToTableFillRow(Object obj, out SqlGuid id, 
 out SqlGuid childId, outint depth, outint childDepth) 
{
 //.. . }

对于由 TreeToTable(...) 中的枚举数返回的每个对象,都调用 TreeToTableFillRow(...) 方法。 这基本上是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

然后打开一个 SqlDataReaderSqlDataReader 读取所有记录并为每个记录创建一个 Node 对象。 在 Node 对象中,存储 IdParentId的值。 这个 Node 对象存储在一个 Dictionary 中,我称之为 source。 现在 root 已经确定,只是因为不需要额外的循环来查找 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 // usedif (id == rootId || rootId.IsNull && parentId.IsNull)
 {
 root = node;
 }
 }
 rdr.Close();
}

它由 Id 存储在 Dictionary 中,因为我们需要在 Id 上进行大量搜索,同时构建节点层次结构:

// Create hierarchyforeach (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-depthroot.CalculateDepth(0);

现在我们有了节点层次结构,在返回结果之前只需要"平展":

// FlattenRowList 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);
}

flattenening是 Node 对象上的一个方法。 它为 result 对象及其子对象添加一个 Row 对象,这只是 Row 类型的List:

publicvoid 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 inthis.SubNodeList())
 {
 Row row2 = new Row(id, node.Id, depth, node.Depth);
 result.Add(row2);
 }
}

这就是所有的,result 现在是 id'sparentId'sdepth'schildDepth's的完整 List。 这里 List 由 TreeToTableFillRow(...) 方法发送到 T-SQL,后者由 sql server处理。

效率

在我看来TreeToTable函数的执行速度相当快。 它每秒处理 20.000条记录,这将导致几乎 130.000行输出,在我的AMD Sempron 2800笔记本上测试。 处理时间是lineair的记录输入数,因为有很多递归和搜索,因这里有很多的递归和搜索。

这个函数可能很快,你应该在使用它时小心。 SQL Server 不能在sql上应用任何查询 optimiziations,结果也无法缓存。

通过添加to子句参数,可以增加性能,以便它不需要处理这么多输入记录。 性能也可以通过添加最大深度参数来获得更好的效果,因这里它不会输出你可以能不使用的行。 我尝试了两个选项,它们在特定的场景中确实。 我认为很少数量的参数使得函数更容易使用,我将它退出了可以用的代码。 使用sql语句,可以获得相同的结果,并且查询更容易阅读。

为了将uniqueidentifiers与测试结合起来,我创建了一个int版本。 令人惊讶的是,int版本运行速度更快。 查看下面的图表。

Timings

创建测试数据

为了创建测试数据,在下载中包含了一个小的Windows 应用程序。

Sample data generator

Points of interest

有其他方法可以实现相同的结果。 存储过程可以生成你想要的任何内容,但是我发现它们很难写出来,而且大部分。 SQL Server 2005的非常流行的自定义 table 表达式保证非常快且易于使用,但是我不能返回相同的结果。 我对备选方案感兴趣,请将它的发送给。

历史记录

这首发行版是在august年发布的。



文章标签:tab      tables  Hiera  

Copyright © 2011 HelpLib All rights reserved.    知识分享协议 京ICP备05059198号-3  |  如果智培  |  酷兔英语