SQL高级分组与CASE语句按系统统计子系统加载耗时方案

问题原因 #

你写的SQL存在两个核心逻辑错误，直接导致执行报错、结果不符合预期：

• 聚合逻辑嵌套错误：你写的SUM(DATEDIFF(s, min(StartTime), max(EndTime)))属于多层嵌套聚合，标准SQL不支持在同一分组层级中对聚合结果再次做聚合计算。而且这里取的min/max是整个系统分组的全局最早/最晚时间，不是单条文件记录的加载时长，就算能执行计算结果也是错的。
• CASE语句位置错误：你把CASE判断写在了SUM聚合的外层，还直接引用了未加入GROUP BY的SubSystem字段。按System分组后，单组内会包含A1、A2、A3多个子系统的记录，SQL引擎无法确定要取哪条记录的SubSystem值做判断，自然会抛出字段需加入GROUP BY的报错。

正确实现方案 #

核心修正思路：把条件判断放到聚合函数内部，单条文件的加载耗时用自身的结束时间减开始时间计算，符合子系统匹配条件的记录才计入对应分类的耗时累加，不符合的记0即可。
以下是适配SQL Server语法（和你原SQL用的DATEDIFF语法一致）的实现代码：

SELECT 
    System,
    MIN([File Load Start Time]) AS [Overall System Load Start Time],
    MAX([File Load End Time]) AS [Overall System Load End Time],
    -- 计算A1子系统累计耗时并格式化
    CONCAT('00:00:', RIGHT('0' + CAST(SUM(
        CASE WHEN [Subsystem & Filename] LIKE 'A1%' 
        THEN DATEDIFF(SECOND, [File Load Start Time], [File Load End Time]) 
        ELSE 0 END
    ) AS VARCHAR(10)), 2)) AS [A1 Time Taken],
    -- 计算A2子系统累计耗时并格式化
    CONCAT('00:00:', RIGHT('0' + CAST(SUM(
        CASE WHEN [Subsystem & Filename] LIKE 'A2%' 
        THEN DATEDIFF(SECOND, [File Load Start Time], [File Load End Time]) 
        ELSE 0 END
    ) AS VARCHAR(10)), 2)) AS [A2 Time Taken],
    -- 计算A3子系统累计耗时并格式化
    CONCAT('00:00:', RIGHT('0' + CAST(SUM(
        CASE WHEN [Subsystem & Filename] LIKE 'A3%' 
        THEN DATEDIFF(SECOND, [File Load Start Time], [File Load End Time]) 
        ELSE 0 END
    ) AS VARCHAR(10)), 2)) AS [A3 Time Taken]
FROM 你的日志表名 -- 替换为实际表名
GROUP BY System

逻辑说明 #

• 系统整体的最早加载开始时间、最晚加载结束时间，直接通过MIN、MAX取分组内对应时间字段的极值即可，和你原有逻辑一致。
• 各子系统累计耗时部分，CASE语句在SUM函数内部逐行判断记录归属的子系统类型，仅对匹配分类的记录计算单文件加载秒数，最终SUM直接累加所有符合条件的单文件耗时，不存在嵌套聚合问题，也不会出现未分组字段引用报错。
• 最后通过字符串拼接+右补零的方式，把累计秒数处理为需求要求的00:00:xx格式，不足两位的秒数会自动补前导零。

如果你用的是MySQL、PostgreSQL等其他数据库，只需要把代码中的时间差计算函数、字符串补零函数替换为对应数据库的等价函数即可，「CASE写在聚合函数内做条件累加」的核心逻辑不需要调整。

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