寻求基于类文件路径层级列值排序SQL查询结果的高效方案

我来给你几个实用的解决方案，刚好之前处理过类似的可变层级路径排序问题，优先说你倾向的SQL存储过程方案，再补充C#端的实现：

一、SQL端：递归CTE实现动态层级排序 #

你的核心痛点是层级不固定，嵌套charindex/substring既麻烦又可能有性能顾虑，而string_split没法直接用于整行排序——递归CTE刚好能解决这个问题，它可以动态拆分任意层级的路径，然后生成可直接排序的键，数千条数据的性能完全不用担心。

假设你的表名为FilePaths，字段是PK、FileName、FilePath，可以用下面的代码实现：

WITH PathCTE AS (
    -- 锚点：拆分路径的第一层
    SELECT 
        PK,
        FileName,
        FilePath,
        1 AS Level,
        -- 提取当前层级的内容（去掉前后空格）
        TRIM(SUBSTRING(FilePath, 1, CHARINDEX('>', FilePath + '>') - 1)) AS CurrentLevel,
        -- 去掉已拆分的部分，留下剩余路径
        STUFF(FilePath, 1, CHARINDEX('>', FilePath + '>'), '') AS RemainingPath
    FROM FilePaths
    UNION ALL
    -- 递归：继续拆分剩余路径的下一层
    SELECT 
        PK,
        FileName,
        FilePath,
        Level + 1,
        TRIM(SUBSTRING(RemainingPath, 1, CHARINDEX('>', RemainingPath + '>') - 1)),
        STUFF(RemainingPath, 1, CHARINDEX('>', RemainingPath + '>'), '')
    FROM PathCTE
    WHERE RemainingPath <> ''
)
-- 生成排序键并排序
SELECT 
    fp.PK,
    fp.FileName,
    fp.FilePath
FROM FilePaths fp
JOIN PathCTE pc ON fp.PK = pc.PK
-- 按层级顺序拼接每个层级内容，生成可直接排序的键
GROUP BY fp.PK, fp.FileName, fp.FilePath
ORDER BY STRING_AGG(pc.CurrentLevel, '|') WITHIN GROUP (ORDER BY pc.Level);

逻辑说明： #

1. 递归CTE会把每个路径拆分成单独的层级条目，比如Folder1 > Foldera > FileName会拆成3条记录，分别对应Level1到Level3的内容；
2. 用STRING_AGG按层级顺序拼接所有层级内容，生成一个类似Folder1|Foldera|FileName的排序键；
3. 直接按这个排序键升序，就实现了你要的“先按第一层排序，再按第二层，以此类推”的逻辑。

如果你的SQL Server版本支持STRING_AGG（2017及以上），这个方案非常高效；如果是旧版本，可以用FOR XML PATH来拼接字符串替代。

二、SQL端备选：固定长度排序键（适合层级上限明确的场景） #

如果你的层级最多就是7层，也可以用预定义的字符串拼接方式，把每个层级补成固定长度（比如50字符），这样拼接后的字符串可以直接排序。优点是比递归CTE更简单，缺点是层级固定，扩展性差：

SELECT 
    PK,
    FileName,
    FilePath
FROM FilePaths
ORDER BY 
    -- 第一层：提取后补到50字符
    LEFT(TRIM(SUBSTRING(FilePath, 1, CHARINDEX('>', FilePath + '>') - 1)) + SPACE(50), 50),
    -- 第二层：先去掉第一层再提取
    LEFT(TRIM(SUBSTRING(STUFF(FilePath, 1, CHARINDEX('>', FilePath + '>'), ''), 1, CHARINDEX('>', STUFF(FilePath, 1, CHARINDEX('>', FilePath + '>'), '') + '>') - 1)) + SPACE(50), 50),
    -- 第三层到第七层以此类推，复制上面的逻辑即可
    LEFT(TRIM(SUBSTRING(STUFF(STUFF(FilePath, 1, CHARINDEX('>', FilePath + '>'), ''), 1, CHARINDEX('>', STUFF(FilePath, 1, CHARINDEX('>', FilePath + '>'), '') + '>'), ''), 1, CHARINDEX('>', STUFF(STUFF(FilePath, 1, CHARINDEX('>', FilePath + '>'), ''), 1, CHARINDEX('>', STUFF(FilePath, 1, CHARINDEX('>', FilePath + '>'), '') + '>'), '') + '>') - 1)) + SPACE(50), 50),
    -- ... 继续写第4到第7层
    LEFT(SPACE(50),50); -- 层级不足的补空格

三、C#端：自定义List排序 #

如果SQL端因为权限或版本限制不好实现，C#端处理起来也很直观，而且数千条数据的排序速度完全没问题：

首先假设你有一个对应数据的实体类：

public class FileItem
{
    public int PK { get; set; }
    public string FileName { get; set; }
    public string FilePath { get; set; }
}

然后从数据库拉取数据到List<FileItem>后，用自定义比较器排序：

var fileItems = // 从数据库获取的List<FileItem>

fileItems.Sort((itemA, itemB) =>
{
    // 拆分路径为层级数组，去掉每个层级的前后空格
    var levelsA = itemA.FilePath.Split('>').Select(s => s.Trim()).ToArray();
    var levelsB = itemB.FilePath.Split('>').Select(s => s.Trim()).ToArray();

    // 逐层级比较
    var minLevelCount = Math.Min(levelsA.Length, levelsB.Length);
    for (int i = 0; i < minLevelCount; i++)
    {
        var compareResult = string.Compare(levelsA[i], levelsB[i], StringComparison.OrdinalIgnoreCase);
        if (compareResult != 0)
        {
            return compareResult;
        }
    }

    // 如果前面的层级都相同，短路径排在前面（比如Folder1 > Foldera 排在 Folder1 > Foldera > File 前面）
    return levelsA.Length.CompareTo(levelsB.Length);
});

逻辑说明： #

1. 把每个路径按>拆分成层级数组；
2. 从第一层开始逐层级比较，只要某一层级有差异就返回比较结果；
3. 如果前面的层级都相同，短路径排在前面，符合你要的层级分组逻辑。

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