SQL Server中标记不含有效文档的文件夹行

需求说明 #

需要标记表中满足以下条件的行：

• 当前行numberOfDocuments（文档数量）为0
• 当前文件夹的所有子文件夹（包括各级嵌套子文件夹）均无文档（即子文件夹的numberOfDocuments都为0）

若当前文件夹文档数为0，但存在任意子文件夹包含文档，则不标记该行。

示例数据 #

首先创建示例表并插入测试数据：

CREATE TABLE FolderStats (
    folderPath NVARCHAR(255) PRIMARY KEY,
    numberOfFolders INT,
    numberOfDocuments INT
);

INSERT INTO FolderStats (folderPath, numberOfFolders, numberOfDocuments)
VALUES
    (N'\Root', 2, 0),
    (N'\Root\FolderA', 1, 0),
    (N'\Root\FolderA\SubA1', 0, 5),
    (N'\Root\FolderB', 0, 0),
    (N'\Root\FolderC', 3, 0),
    (N'\Root\FolderC\SubC1', 0, 0),
    (N'\Root\FolderC\SubC2', 1, 0),
    (N'\Root\FolderC\SubC2\SubSubC2', 0, 0);

T-SQL 查询解决方案 #

使用NOT EXISTS子查询判断当前文件夹是否存在带文档的子文件夹，结合条件实现标记：

SELECT
    folderPath,
    numberOfFolders,
    numberOfDocuments,
    CASE
        WHEN numberOfDocuments = 0 
             AND NOT EXISTS (
                 SELECT 1
                 FROM FolderStats AS child
                 WHERE (child.folderPath = folderPath + N'\' OR child.folderPath LIKE folderPath + N'\%')
                       AND child.numberOfDocuments > 0
             ) THEN '需要标记'
        ELSE '无需标记'
    END AS IsMarked
FROM FolderStats;

代码逻辑说明 #

1. 首先筛选当前行文档数为0的记录
2. 通过NOT EXISTS检查是否存在任何子文件夹：
   • 子文件夹路径需以当前文件夹路径加分隔符\开头（避免前缀匹配错误，比如区分\Root和\RootFolder）
   • 子文件夹的numberOfDocuments大于0
3. 若不存在此类子文件夹，则标记为「需要标记」，否则为「无需标记」

期望输出 #

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