sql - 使用 SQL Server 递归公用表表达式获取文件夹中所有文件的完整路径(带子文件夹)

Question

有一个 SQL Server 未公开的扩展存储过程，名为 xp_dirtree ，它可以以表格格式返回所有文件和文件夹名称(包括子文件夹)。为了练习我对递归 CTE 的理解，我决定使用它来获取指定文件夹(包括子文件夹)中所有文件的完整路径。但是，经过一个小时的挠头后，我仍然想不出正确的方法。以下代码是我目前所拥有的。这个目的可以用递归 CTE 来实现吗？

DECLARE @dir NVARCHAR(260) ;
SELECT  @dir = N'c:\temp' ;

IF RIGHT(@dir, 1) <> '\' 
    SELECT  @dir = @dir + '\' ;

IF OBJECT_ID('tempdb..#dirtree', 'U') IS NOT NULL 
    DROP TABLE #dirtree ;
CREATE TABLE #dirtree
(
 id INT PRIMARY KEY
        IDENTITY,
 subdirectory NVARCHAR(260),
 depth INT,
 is_file BIT
) ;

INSERT  INTO #dirtree
        EXEC xp_dirtree 
            @dir,
            0,
            1 ;

SELECT  *
FROM    #dirtree ;

WITH    files
          AS (
              SELECT    id,
                        subdirectory,
                        depth,
                        is_file, subdirectory AS path
              FROM      #dirtree
              WHERE     is_file = 1
                        AND depth <> 1
   UNION ALL
               -- ...
             )
    SELECT  *
    FROM    files ;

假设 xp_dirtree 输出是:

/*
id  subdirectory   depth   is_file
--- -------------- ------- -------
1   abc.mdf        1       1
2   a              1       0
3   a.txt          2       1
4   b.txt          2       1
5   a.rb           1       1
6   aaa.flv        1       1
*/

我想要的是:

/*
path
------------------
c:\temp\abc.mdf
c:\temp\a\a.txt
c:\temp\a\b.txt
c:\temp\a.rb
c:\temp\aaa.flv
*/

Answer 1

如果我理解你是正确的，你想要这样的东西:

测试数据:

CREATE TABLE #dirtree
(
    id INT,
    subdirectory NVARCHAR(260),
    depth INT ,
    is_file BIT,
    parentId INT
)

INSERT INTO #dirtree(id,subdirectory,depth,is_file)
VALUES
    (1,'abc.mdf',1,1),(2,'a',1,0),(3,'a.txt',2,1),
    (4,'b.txt',2,1),(5,'a.rb',1,1),(6,'aaa.flv',1,1)

更新了父 ID

UPDATE #dirtree
SET ParentId = (SELECT MAX(Id) FROM #dirtree
      WHERE Depth = T1.Depth - 1 AND Id < T1.Id)
FROM #dirtree T1

查询

;WITH CTE
AS
(
    SELECT
        t.id,
        t.subdirectory,
        t.depth,
        t.is_file
    FROM
        #dirtree AS t
    WHERE
        is_file=0
    UNION ALL
    SELECT
        t.id,
        CAST(CTE.subdirectory+'\'+t.subdirectory AS NVARCHAR(260)),
        t.depth,
        t.is_file
    FROM
        #dirtree AS t
        JOIN CTE
            ON CTE.id=t.parentId
    )
SELECT
    'c:\temp\'+CTE.subdirectory AS [path]
FROM
    CTE
WHERE
    CTE.is_file=1
UNION ALL
SELECT
    'c:\temp\'+t.subdirectory
FROM
    #dirtree AS t
WHERE
    is_file=1
    AND NOT EXISTS
    (
        SELECT
            NULL
        FROM
            CTE
        WHERE
            CTE.id=t.id
    )

结果

path
---------------
c:\temp\a\a.txt
c:\temp\a\b.txt
c:\temp\abc.mdf
c:\temp\a.rb
c:\temp\aaa.flv

编辑

将示例中使用的表格更改为更像您问题中的表格