如何用SQL按各代理专属日期范围跨表统计销售总额

核心逻辑思路：先把符合条件的活跃代理的专属统计日期范围提前计算成型，再通过关联条件匹配合法的销售记录做聚合，全程用左连接保证无销售记录的代理也能完整保留基础信息，不会被销售表的过滤逻辑误删。

关键实现细节 #

• 先修正原查询草稿的语法bug：原Case语句多写了一个闭合右括号，直接执行会触发语法报错
• 日期匹配采用闭区间判断，确保SalesDate刚好等于区间首尾日期的销售记录不会被漏算
• 不要直接拿代理基础明细和销售明细做硬连后再分组，提前固化代理维度范围可以避免因销售明细行数过多导致的查询性能问题

可运行SQL代码 #

WITH AgentDateRange AS (
    -- 计算每个活跃代理的专属统计时间窗口
    SELECT
        bID,
        pID,
        aID AS AgentID,
        NAME,
        aStart,
        CONVERT(DATE, '2021-03-01') AS Date_from,
        -- 统计截止日为「代理入职满365天节点」与「2022-02-28」的较小值
        CASE
            WHEN DATEADD(DAY, 364, aStart) <= '2022-02-28' THEN DATEADD(DAY, 364, aStart)
            ELSE CONVERT(DATE, '2022-02-28')
        END AS Date_end
    FROM Agents AS a
    WHERE
        STATUS = 'Active'
        AND aStart >= '2020-03-02'
)
SELECT
    adr.bID,
    adr.pID,
    adr.AgentID,
    adr.NAME,
    adr.aStart,
    adr.Date_from,
    adr.Date_end,
    -- 无匹配销售记录的代理默认销售额为0，替代原来的空字符串占位
    ISNULL(SUM(s.SalesSum), 0) AS Sales
FROM AgentDateRange AS adr
LEFT JOIN Sales AS s
    -- 关联条件必须包含日期区间判断，写在ON子句中才不会破坏左连接特性
    ON adr.AgentID = s.aID
    AND s.SalesDate >= adr.Date_from
    AND s.SalesDate <= adr.Date_end
GROUP BY
    adr.bID,
    adr.pID,
    adr.AgentID,
    adr.NAME,
    adr.aStart,
    adr.Date_from,
    adr.Date_end

常见踩坑提示 #

• 如果把销售日期的区间判断写在WHERE子句中，左连接会被隐式转换为内连接，没有销售记录的代理会直接被过滤掉，必须把所有针对销售表的过滤条件放到关联的ON子句中
• 如果后续需要对销售的Product、Kind维度做筛选，同样要把对应条件放到Sales关联的ON子句中，避免丢失无销售的代理行
• 数据库为SQL Server 2022及以上版本时，Date_end的计算可以简化为LEAST(DATEADD(DAY, 364, aStart), '2022-02-28')，不需要写Case判断

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