SQL:在拆分层次结构中查找丢失的文件夹路径 [英] SQL: Find missing folders paths in splitting hierarchies
问题描述
我有一个包含文件夹路径的表.该表包含四列:DirID、BaseDirID、DirLevel 和 DisplayPath.DirID - 文件夹的 ID.BaseDirID - 层次结构中第一个文件夹的 ID.因此，来自同一层次结构的所有文件夹(路径)在此列中共享相同的值.DirLevel - 文件夹的深度.DisplayPath - 文件夹的路径.
I have a table which contains folders paths.
This table contains four columns: DirID, BaseDirID, DirLevel and DisplayPath.
DirID - The folder's ID.
BaseDirID - The ID of the first folder in the hierarchy. So all the folders (paths) from the same hierarchy share the same value in this column.
DirLevel - The depth of the folder.
DisplayPath - The folder's path.
我需要找到层次结构中这些文件夹之间的所有差距".
I need to find all the "gaps" between those folders in the hierarchy.
示例数据例如:
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'
所以我们需要找到以下数据:
So we need to find the following data:
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 条文件夹记录，因此我们寻求最有效的方法，否则脚本将卡住很长时间，我们没有时间.
a. This table contains more than 250,000 records of folders, so we seek for the most efficient way to do so, otherwise the script will be stuck for long time, time we don't have.
B.我没有所有文件夹的列表.我拥有的是根"文件夹和叶"文件夹，我需要在层次结构中找到它们之间的差距".
b. I don't have list of all folders. What I have are the "root" folders and the "leafs" folders which I need to find the "gaps" between them in the hierarchy.
c.该表可以包含多个层次结构，我们需要找到所有层次结构中的差距".
c. The table can contains more than one hierarchy and we need to find the "gaps" in all of the hierarchies.
d.每个层次结构都可以拆分，正如您在示例数据中看到的那样，第一个层次结构从A\B"文件夹拆分为两个文件夹路径:'A\B\C' 和 'A\B\F'.第二层级从U\V"文件夹拆分为两个文件夹路径:U\V\W"和U\V\M".即使在这种层次结构分裂的情况下，我们也需要找到所有的差距".
e.sql server 版本为:SQL 2012 SP3.
e. The sql server version is: SQL 2012 SP3.
此问题是以下链接中提出的问题的延续问题:SQL:在表我们的问题还包括以粗体显示的第 4 条评论.
This question is a continuation question for the question which asked in the following link: SQL: Find missing hierarchy Folders (Paths) in a table Our question include also the 4th comment which appear in bold.
我看到有一个名为hierarchyid"的新列类型(从 sql server 2008 开始)，我认为这可能对我们有帮助 - https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference你怎么看?
I saw that there is a new column type which called "hierarchyid" (start from sql server 2008), which I thought that might help us - https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference What do you think?
提前致谢.
推荐答案
使用这个添加的路径 (11,2,'U\V\Z\L\O\Q\R\S\T') 显示路径中多个丢失的文件夹:
Using this added path (11,2,'U\V\Z\L\O\Q\R\S\T') to show multiple missing folders in a path:
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
rextester demo: 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 |
+-----------+-----------------+
这篇关于SQL:在拆分层次结构中查找丢失的文件夹路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
