如何在T-SQL中按深度优先顺序排序DeltaHistory表的文件路径

我有一张存储文件系统扫描数据的DeltaHistory表，表结构如下：

CREATE TABLE DeltaHistory 
(
    file_id INT IDENTITY(1,1) PRIMARY KEY,
    source_id INT NOT NULL,
    file_path NVARCHAR(500) NOT NULL,
    file_path_hash VARCHAR(64) NOT NULL,
    file_name NVARCHAR(255) NOT NULL,
    file_size BIGINT NOT NULL,
    file_create_date DATETIME2 NOT NULL,
    last_modified DATETIME2 NOT NULL,
    source_specific_metadata NVARCHAR(MAX),
    scan_id INT NOT NULL,
    change_type VARCHAR(10) NOT NULL 
        CHECK (change_type IN ('CREATE', 'UPDATE', 'DELETE')),
    FOREIGN KEY (source_id) REFERENCES Source(source_id),
    FOREIGN KEY (scan_id) REFERENCES ScanHistory(scan_id)
);

给定source_id和scan_id，需要按**深度优先顺序（非字典序）**对file_path排序，具体要求：

• 文件夹内的所有文件需先于其子文件夹中的文件显示
• 字典序排序因字符串比较方式无法满足文件夹层级排序需求
• 排序时可忽略用户指定的前缀（例如\\localhost\FileSystem\test_fs）

示例对比 #

字典序排序结果（不符合需求）：

\\localhost\FileSystem\test_fs\rGdS27इh.txt  
\\localhost\FileSystem\test_fs\root_00_cउQs\5sऔbcइmक.data  
\\localhost\FileSystem\test_fs\USCऐक5lQ.json  

期望的深度优先排序结果（文件优先于子文件夹）：

\\localhost\FileSystem\test_fs\rGdS27इh.txt  
\\localhost\FileSystem\test_fs\USCऐक5lQ.json  
\\localhost\FileSystem\test_fs\root_00_cउQs\5sऔbcइmक.data  

最终输出需符合如下格式：

\\localhost\FileSystem\test_fs\rGdS27इh.txt
\\localhost\FileSystem\test_fs\UNsklIK4.data
\\localhost\FileSystem\test_fs\root_00_cउQs\5sऔbcइmक.data
\\localhost\FileSystem\test_fs\root_00_cउQs\1yउ9OIइM\5.data
\\localhost\FileSystem\test_fs\root_00_cउQs\1yउ9OIइM\2yउ9OIइM\5.data
\\localhost\FileSystem\test_fs\root_00_cउQs\2yउ9OIइM\5.data
\\localhost\FileSystem\test_fs\root_00_cउQs\2yउ9OIइM\2yउ9OIइM\5.data
\\localhost\FileSystem\test_fs\root_01_cउQs\5sऔbcइmक.data
\\localhost\FileSystem\test_fs\root_01_cउQs\1yउ9OIइM\5.data
\\localhost\FileSystem\test_fs\root_01_cउQs\1yउ9OIइM\2yउ9OIइM\5.data
\\localhost\FileSystem\test_fs\root_01_cउQs\2yउ9OIइM\5.data
\\localhost\FileSystem\test_fs\root_01_cउQs\2yउ9OIइM\2yउ9OIइM\5.data

核心实现思路 #

要实现这种特殊排序，关键是给每个路径生成自定义排序键，让数据库能按我们的规则排序：

1. 剥离前缀：移除指定的路径前缀，只处理相对路径，避免前缀干扰层级判断。
2. 区分文件/文件夹：通过文件名后缀（或结合file_size字段）标记记录类型，确保同层级下文件排在文件夹前面。
3. 构建层级排序键：将路径按\拆分成各层级节点，给每个节点前添加类型标记（文件用0，文件夹用1），拼接成排序键。这样既保证文件优先，又能通过排序键的长度和内容实现深度优先排序。

示例T-SQL查询 #

方法一：递归CTE（适合层级较深的路径） #

递归CTE可以精确拆分每个路径层级，生成准确的排序键：

DECLARE @prefix NVARCHAR(500) = N'\\localhost\FileSystem\test_fs';
DECLARE @source_id INT = 1; -- 替换为实际source_id
DECLARE @scan_id INT = 100; -- 替换为实际scan_id

WITH PathCTE AS (
    -- 预处理：剥离前缀，标记文件/文件夹，转换为XML方便拆分层级
    SELECT 
        file_path,
        REPLACE(file_path, @prefix, N'') AS relative_path,
        -- 标记是否为文件夹：文件名无后缀则视为文件夹，可根据实际逻辑调整
        CASE WHEN CHARINDEX(N'.', REVERSE(file_name)) > 0 THEN 0 ELSE 1 END AS is_folder,
        CAST('<path><node>' + REPLACE(REPLACE(file_path, @prefix, N''), N'\', N'</node><node>') + N'</node></path>' AS XML) AS path_xml
    FROM DeltaHistory
    WHERE source_id = @source_id AND scan_id = @scan_id
),
PathHierarchy AS (
    -- 递归拆分路径层级，生成排序键
    SELECT
        file_path,
        relative_path,
        is_folder,
        path_xml,
        1 AS level,
        path_xml.value('(/path/node[1])[1]', NVARCHAR(255)) AS current_node,
        -- 初始排序键：类型标记+节点名称
        CAST(is_folder AS VARCHAR(1)) + N'_' + path_xml.value('(/path/node[1])[1]', NVARCHAR(255)) AS sort_key
    FROM PathCTE
    WHERE relative_path <> N'' -- 排除前缀本身（如果存在）
    UNION ALL
    SELECT
        ph.file_path,
        ph.relative_path,
        ph.is_folder,
        ph.path_xml,
        ph.level + 1 AS level,
        ph.path_xml.value('(/path/node[sql:column("ph.level")+1])[1]', NVARCHAR(255)) AS current_node,
        -- 拼接下一层级的排序键
        ph.sort_key + N'_' + CAST(ph.is_folder AS VARCHAR(1)) + N'_' + ph.path_xml.value('(/path/node[sql:column("ph.level")+1])[1]', NVARCHAR(255)) AS sort_key
    FROM PathHierarchy ph
    WHERE ph.level < ph.path_xml.value('count(/path/node)', INT)
)
-- 按完整排序键排序，输出结果
SELECT DISTINCT ph.file_path
FROM PathHierarchy ph
ORDER BY 
    ph.sort_key, -- 按自定义排序键实现深度优先+文件优先
    ph.is_folder; -- 兜底：同排序键下文件在前

方法二：简化版（适合层级较浅的路径） #

如果路径层级不多，可以直接通过字符串替换生成排序键，无需递归：

DECLARE @prefix NVARCHAR(500) = N'\\localhost\FileSystem\test_fs';
DECLARE @source_id INT = 1;
DECLARE @scan_id INT = 100;

SELECT 
    file_path
FROM (
    SELECT 
        file_path,
        -- 生成排序键：每个路径节点前添加类型标记
        REPLACE(
            REPLACE(file_path, @prefix, N''),
            N'\', 
            N'_' + CAST(CASE WHEN CHARINDEX(N'.', REVERSE(file_name)) > 0 THEN '0' ELSE '1' END AS VARCHAR(1)) + N'_'
        ) AS sort_key,
        CASE WHEN CHARINDEX(N'.', REVERSE(file_name)) > 0 THEN 0 ELSE 1 END AS is_folder
    FROM DeltaHistory
    WHERE source_id = @source_id AND scan_id = @scan_id
) t
ORDER BY 
    sort_key, -- 按自定义排序键排序
    is_folder; -- 文件优先

关键说明 #

• 文件/文件夹判断：示例中通过文件名是否包含.判断，实际可结合file_size（文件夹大小通常为0）或source_specific_metadata中的字段，提升判断准确性。
• 前缀处理：使用REPLACE移除前缀，确保排序仅针对相对路径。如果前缀可能出现在路径中间，可改用STUFF函数从开头截取相对路径。
• 排序键逻辑：通过在每个节点前添加0/1标记，保证同层级文件优先；层级越深的路径排序键越长，自然实现深度优先的遍历顺序。

内容的提问来源于stack exchange，提问作者Himanshu Garg