SQL Server查询报错：无法嵌套使用聚合函数，求解决方案

你遇到的错误是因为在聚合函数（如MAX()）内部嵌套了另一个聚合函数，SQL Server不允许这种写法。要获取每个分组（每日、团队、Agent）中最长等待时长对应的CallStart和CallEnd，可以通过窗口函数或APPLY关联查询来实现，以下是两种可行方案：

方案一：使用窗口函数ROW_NUMBER() #

先通过窗口函数对每个分组内的记录按等待时长降序排名，标记出等待时长最长的那条记录，再进行聚合统计：

WITH ranked_contacts AS (
    SELECT
        CAST(CallStart AS DATE) AS call_date,
        team,
        agent,
        talk_time,
        wait_time,
        CallStart,
        CallEnd,
        -- 按日期、团队、Agent分组，等待时长最长的记录标记为1
        ROW_NUMBER() OVER (PARTITION BY CAST(CallStart AS DATE), team, agent ORDER BY wait_time DESC) AS rn
    FROM tbl_contacts
)
SELECT
    call_date,
    team,
    agent,
    AVG(talk_time) AS avg_talk_time,
    MAX(wait_time) AS max_wait_time,
    -- 提取分组内等待时长最长的通话开始/结束时间
    MAX(CASE WHEN rn = 1 THEN CallStart END) AS max_wait_call_start,
    MAX(CASE WHEN rn = 1 THEN CallEnd END) AS max_wait_call_end
FROM ranked_contacts
GROUP BY call_date, team, agent;

方案二：使用CROSS APPLY关联查询 #

通过CROSS APPLY直接关联每个分组内等待时长最长的单条记录，再聚合统计：

SELECT
    CAST(c.CallStart AS DATE) AS call_date,
    c.team,
    c.agent,
    AVG(c.talk_time) AS avg_talk_time,
    MAX(c.wait_time) AS max_wait_time,
    top_wait_call.CallStart AS max_wait_call_start,
    top_wait_call.CallEnd AS max_wait_call_end
FROM tbl_contacts c
CROSS APPLY (
    -- 筛选当前分组内等待时长最长的通话记录
    SELECT TOP 1 CallStart, CallEnd
    FROM tbl_contacts
    WHERE CAST(CallStart AS DATE) = CAST(c.CallStart AS DATE)
      AND team = c.team
      AND agent = c.agent
    ORDER BY wait_time DESC
) top_wait_call
GROUP BY CAST(c.CallStart AS DATE), c.team, c.agent, top_wait_call.CallStart, top_wait_call.CallEnd;

注意事项 #

• 如果同一个分组内存在多条等待时长相同且都是最大值的记录，方案一的ROW_NUMBER()会随机取其中一条；若要保留所有并列最大值的记录，可改用RANK()代替ROW_NUMBER()，并调整后续聚合逻辑。
• 确保CallStart、wait_time等字段不为空，否则需根据实际业务添加ISNULL()处理。

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