SQL：查找表中缺失的层次结构文件夹（路径）

Question

SQL：查找表中缺失的层次结构文件夹（路径）

sqlsql-serversqlperformancesql-server-performance

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条记录的文件夹，因此我们需要寻找最有效的方法来执行操作，否则脚本会卡住很长时间，而我们没有这样的时间。

注释：我没有所有文件夹的列表。我只有“根”文件夹和“叶子”文件夹，我需要在它们之间的层次结构中找到“空缺”。

第二个注释：该表可以包含多个层次结构，我们需要在所有层次结构中找到“空缺”。为此，还有两个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'

事先感谢。

- Nuriel Zrubavely

这是我会在 SQL 之外完成的任务。在这种情况下，任何编程语言都可能比 T-SQL 更胜一筹。 - Zohar Peled

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Pரதீப் · Accepted Answer

以下是一种使用递归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/

（注：本文主要介绍针对 IT 技术中的 Tally 表的使用，原文详见链接）