SQL异常聚合，寻求帮助

Question

我有以下场景

tbody> <<tr>αααα

System	Subsystem &;文件名	文件加载开始时间	文件加载结束时间
α	A1 transactiontxt	2022-06-19 08:00:00	2022-06-19 08:00:02
	A2 userscsv	2022-06-19 08:00:02	2022-06-19 08:00:05
	A2 employeescsv	2022-06-19 08:00:05	2022-06-19 08:00:08
	A1 managerscsv	2022-06-19 08:00:00	2022-06-19 08:00:02
	A3 customerscsv	2022-06-19 08:00:01	2022-06-19 08:00:04
γ	A1 transactiontxt	2022-06-19 10:00:48	2022-06-19 10:00:53
γ	A2 userscsv	2022-06-19 10:00:53	2022-06-19 10:00:54
γ	A2 employeescsv	2022-06-19 10:00:27	2022-06-19 10:00:30
γ	A1 managerscsv	2022-06-19 10:00:11	2022-06-19 10:00:17
γ	A3 customerscsv	2022-06-19 10:00:13	2022-06-19 10:00:14

聚合函数不能在另一个聚合函数SUM(... MIN(...), MAX(...))中使用。为了简化查询，您需要将执行的操作拆分为:

• 检测数据的唯一行
• 提取子系统"源自">子系统&文件名,
• 在子系统上的旋转
• 对您的相关字段进行聚合

WITH cte AS (
SELECT DISTINCT [System],
LEFT([Subsystem & Filename], 
CHARINDEX(' ', [Subsystem & Filename])-1) AS [Subsystem],
[File Load Start Time]                         AS [StartTime],
[File Load End Time]                           AS [EndTime]
FROM tab
)   
SELECT [System],
MIN([StartTime])        AS [Overall System Load Start Time],
MAX([EndTime])          AS [Overall System Load End Time],
SUM(CASE WHEN [Subsystem]='A1' 
THEN DATEDIFF(s, [StartTime], [EndTime]) END) AS [A1 Time Taken],
SUM(CASE WHEN [Subsystem]='A2' 
THEN DATEDIFF(s, [StartTime], [EndTime]) END) AS [A2 Time Taken],
SUM(CASE WHEN [Subsystem]='A3' 
THEN DATEDIFF(s, [StartTime], [EndTime]) END) AS [A3 Time Taken]
FROM cte
GROUP BY [System]

点击这里查看演示。