SQL:在表中查找缺少的层次结构文件夹(路径)

Question

我有一个包含文件夹路径的表格。我需要找到层次结构中这些文件夹之间的所有“差距”。我的意思是，如果表格包含这 3 个文件夹:

'A'
'A\B\C'
'A\B\C\D\E\F\G'

我需要在层次结构中找到以下丢失的文件夹:

'A\B'
'A\B\C\D'
'A\B\C\D\E'
'A\B\C\D\E\F'

这个表包含超过250,000条记录的文件夹，所以我们寻求最高效的方法，否则脚本会卡很久，时间我们没有。

评论:我没有所有文件夹的列表。我拥有的是“根”文件夹和“叶”文件夹，我需要它们在层次结构中找到它们之间的“差距”。

第二条评论:该表可以包含多个层次结构，我们需要找到所有层次结构中的“间隙”。就此而言，还有 2 个其他 int 列:“DirID”和“BaseDirID”。 “DirID”列是我们表中的 id 列。 “BaseDirID”包含层次结构中第一个文件夹的 ID。因此，同一层次结构中的所有文件夹(路径)在此列中共享相同的值。示例数据例如:

DirID   BaseDirID   DisplayPath
1   1   'A'
2   1   'A\B\C'
3   1   'A\B\C\D\E'
4   4   'U'
5   4   'U\V\W'
6   4   'U\V\W\X\Y'

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

BaseDirID   DisplayPath
1   'A\B'
1   'A\B\C\D'
4   'U\V'
4   'U\V\W\X'

提前致谢。

Answer 1

这是一种使用Recursive CTE 和拆分字符串函数的方法

;WITH existing_hierachies
     AS (SELECT DirID,
                BaseDirID,
                DisplayPath
         FROM   (VALUES (1,1,'A' ),
                        (2,1,'A\B\C' ),
                        (3,1,'A\B\C\D\E' ),
                        (4,4,'U' ),
                        (5,4,'U\V\W' ),
                        (6,4,'U\V\W\X\Y' )) tc (DirID, BaseDirID, DisplayPath) ),
     folders_list
     AS (SELECT ItemNumber,
                item fol,
                BaseDirID
         FROM   (SELECT row_number()over(partition by BaseDirID order by Len(DisplayPath) DESC)rn,*
                 FROM   existing_hierachies) a
                 CROSS apply dbo.[Delimitedsplit8k](DisplayPath, '\')
                 Where Rn = 1),
     rec_cte
     AS (SELECT *,
                Cast(fol AS VARCHAR(4000))AS hierar
         FROM   folders_list
         WHERE  ItemNumber = 1
         UNION ALL
         SELECT d.*,
                Cast(rc.hierar + '\' + d.fol AS VARCHAR(4000))
         FROM   rec_cte rc
                JOIN folders_list d
                  ON rc.BaseDirID = d.BaseDirID
                  AND d.ItemNumber = rc.ItemNumber + 1)
SELECT rc.BaseDirID,
       rc.hierar AS Missing_Hierarchies
FROM   rec_cte rc
WHERE  NOT EXISTS (SELECT 1
                   FROM   existing_hierachies eh 
                   WHERE  eh.BaseDirID = rc.BaseDirID 
                     AND  eh.DisplayPath = rc.hierar) 
Order by rc.BaseDirID

结果:

+-----------+---------------------+
| BaseDirID | Missing_Hierarchies |
+-----------+---------------------+
|         1 | A\B                 |
|         1 | A\B\C\D             |
|         4 | U\V                 |
|         4 | U\V\W\X             |
+-----------+---------------------+

---

字符串拆分功能代码

CREATE FUNCTION [dbo].[DelimitedSplit8K]
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover NVARCHAR(4000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO

引用自 http://www.sqlservercentral.com/articles/Tally+Table/72993/