SQL:在拆分层次结构中查找丢失的文件夹路径

sql-server sql-server-2012 (1)

我有一个包含文件夹路径的表。该表包含四列： DirID ， BaseDirID ， DirLevel和DisplayPath 。 DirID - 文件夹的ID。 BaseDirID - 层次结构中第一个文件夹的ID。因此，来自同一层次结构的所有文件夹（路径）在此列中共享相同的值。 DirLevel - 文件夹的深度。 DisplayPath - 文件夹的路径。

我需要找到层次结构中这些文件夹之间的所有“空白”。

示例数据例如：

DirID BaseDirID DirLevel DisplayPath
1   1  1  'A'
2   1  3  'A\B\C'
3   1  5  'A\B\C\D\E'
4   1  3  'A\B\F'
5   1  5  'A\B\F\G\H'
6   2  1  'U'
7   2  3  'U\V\W'
8   2  5  'U\V\W\X\Y'
9   2  3  'U\V\M'
10  2  5  'U\V\M\L\O'

所以我们需要找到以下数据：

BaseDirID DisplayPath
1   'A\B'
1   'A\B\C\D'
1   'A\B\F\G'
2   'U\V'
2   'U\V\W\X'
2   'U\V\M\L'

注释：

一个。这个表格包含超过250,000个文件夹的记录， 所以我们寻求最有效的方法来做到这一点 ，否则脚本会被卡住很长时间，我们没有时间。

湾我没有所有文件夹的列表。我有什么是“根”文件夹和“叶子”文件夹，我需要找到它们之间的“差距”的层次结构。

C。该表可以包含多个层次结构，我们需要找到所有层次结构中的“缺口”。

d。 每个层次结构都可以拆分，正如您在示例数据中所看到的，第一个层次结构拆分为“A \ B”文件夹中的两个文件夹路径：“A \ B \ C”和“A \ B \ F”。 第二层从“U \ V”文件夹分割为两个文件夹路径：“U \ V \ W”和“U \ V \ M”。 即使在层次分裂的情况下，我们也需要找到所有的“差距”。

即 SQL Server版本是：SQL 2012 SP3。

这个问题是在下面的链接中提出的问题的继续问题： SQL：在表中查找缺少的层次文件夹（路径）我们的问题还包括第四个以粗体显示的注释。

我看到有一个新的列类型，称为“hierarchyid”（从SQL Server 2008开始），我认为这可能会帮助我们 - https://docs.microsoft.com/en-us/sql/t-sql/ data-types / hierarchyid-data-type-method-reference你怎么看？

提前致谢。

使用这个添加的路径(11,2,'U\V\Z\L\O\Q\R\S\T')在路径中显示多个丢失的文件夹：

with cte as (
select BaseDirID, DisplayPath = left(DisplayPath,len(DisplayPath)-charindex('\',reverse(DisplayPath)))
from t
where DirLevel > 1
  and not exists (
  select 1 
  from t i
  where t.BaseDirId = i.BaseDirId
    and i.DisplayPath = left(t.DisplayPath,len(t.DisplayPath)-charindex('\',reverse(t.DisplayPath)))
    )
union all 
select BaseDirID, DisplayPath = left(DisplayPath,len(DisplayPath)-charindex('\',reverse(DisplayPath)))
from cte t
where not exists (
  select 1 
  from t i
  where t.BaseDirId = i.BaseDirId
    and i.DisplayPath = left(t.DisplayPath,len(t.DisplayPath)-charindex('\',reverse(t.DisplayPath)))                                                   
    )
)
select distinct * 
from cte

rextester演示： http ://rextester.com/CEVGZ96613

收益：

+-----------+-----------------+
| BaseDirID |   DisplayPath   |
+-----------+-----------------+
|         1 | A\B             |
|         1 | A\B\C\D         |
|         1 | A\B\F\G         |
|         2 | U\V             |
|         2 | U\V\M\L         |
|         2 | U\V\W\X         |
|         2 | U\V\Z           |
|         2 | U\V\Z\L         |
|         2 | U\V\Z\L\O       |
|         2 | U\V\Z\L\O\Q     |
|         2 | U\V\Z\L\O\Q\R   |
|         2 | U\V\Z\L\O\Q\R\S |
+-----------+-----------------+