sql - 如何在 SQL Server 中有效地合并两个层次结构？

Question

我有两个带有hierarchyid 字段的表，其中一个是带有需要合并到另一个中的新数据的临时表(即需要添加到主树中的一组节点，其中一些可能已经是那里)。

除了定义树结构(父/子关系)的hierarchyid 列之外。每个表都有一个单独的列，其中包含唯一标识每个节点的节点标识符。也就是说，判断临时表中的节点是否已经在主表中的方法是通过节点 ID，而不是通过hierarchyid 列。

当务之急是，需要执行的处理看起来像这样:

For each row, RS, in the staging table:
    If there is not already a row with the same Id as RS in the main table:
         Find the parent, PS, of the staging row
         Find the row, PM, in the main table that has the same node ID as PS
         Create a new child, RM of row PM
         Set PM's ID equal to the ID of RS

重要的是，这种方法只有在临时表中的树以广度优先顺序排序/遍历时才有效——这样当遇到 RS 时，可以保证它的父 PS 在主表中已经有相应的行。

到目前为止，我可以看到在 SQL Server 中实现此目的的唯一方法是在临时表(已经排序)上使用游标，并为每一行调用一个存储过程，该过程基本上完全按照上述方式执行，并使用 SELECT 完成MAX() 查找作为 PM 的 child 已经存在的最高层级 ID，以便可以唯一添加 child 。

但是，这是一种非常低效的方法，而且对于我的目的来说太慢了。有没有更好的办法？

作为背景，这是我正在做的一种可行性检查。我需要弄清楚我是否可以在 SQL Server 中快速执行此操作。如果事实证明我不能，我将不得不在数据库之外以另一种方式做。树的合并是问题域所固有的(实际上，在某种意义上是)问题域，因此以不同的方式构建数据或采取更广泛的观点并试图以某种方式完全避免执行此操作不是一种选择。

更新

根据要求，这是一个具体的例子。

表“staging”和“main”都有相同的两列:

   hierarchy_id of type hierarchyid
   node_id of type bigint

初始内容

主要的:

 hierarchy_id    node_id
 /1/             1
 /1/1/           2
 /1/2/           3
 /1/3/           4

分期:

 hierarchy_id    node_id
 /1/             1
 /1/1/           3
 /1/2/           5
 /1/1/1/         6

想要的内容

主要的:

 hierarchy_id    node_id
 /1/             1
 /1/1/           2
 /1/2/           3
 /1/3/           4
 /1/4/           5
 /1/2/1/         6

请注意，临时表中具有hierarchy_id/1/1/的节点对应于目标表中具有hiearchy_id/1/2/的节点(这就是node_id 很重要的原因——不能只是复制hierarchy_id 值)。另请注意，node_id 为 6 的新节点被添加为正确父节点的子节点，即 node_id 为 3 的节点，这就是 hierarchy_id 很重要的原因 - 它定义了任何新节点的树结构(父/子关系)。任何解决方案都需要兼顾这两方面。

Answer 1

我们一直在研究需要类似解决方案的产品。经过对这种方法和其他方法的大量研究，我们得出结论，hierarchyID 方法不适合我们。

因此，作为对您问题的直接回答:使用这种方法没有更好的方法来做到这一点。

看看Nested Set Models并在 Adjacency List Model .

对于这一特定的设计挑战，这两种解决方案都更加优雅和高效。

编辑:
作为事后的想法，万一您没有使用 SQL - 使用非关系数据库可以更好地解决这个问题。
我们不能那样做，因为没有人在设计非关系型数据库方面有足够的专业知识，但如果 SQL 是可选的，那么您可以在 MongoDB 中以更好、更有效的方式使用您当前的方法。