SQL Server多日期区间分组聚合销售额与销量方法求助

你可以直接使用条件聚合实现需求，无需额外关联其他表，参考SQL代码如下：

SELECT
    Product,
    [Period 1 start],
    [Period 1 end],
    [Period 2 start],
    [Period 2 end],
    SUM(CASE WHEN [Sales Date] BETWEEN [Period 1 start] AND [Period 1 end] THEN [Sales Value] ELSE 0 END) AS [Period 1 Sales Value],
    SUM(CASE WHEN [Sales Date] BETWEEN [Period 1 start] AND [Period 1 end] THEN [Sales Qty] ELSE 0 END) AS [Period 1 Sales Qty],
    SUM(CASE WHEN [Sales Date] BETWEEN [Period 2 start] AND [Period 2 end] THEN [Sales Value] ELSE 0 END) AS [Period 2 Sales Value],
    SUM(CASE WHEN [Sales Date] BETWEEN [Period 2 start] AND [Period 2 end] THEN [Sales Qty] ELSE 0 END) AS [Period 2 Sales Qty]
FROM
    你的现有表名 -- 替换为你Cross Join后得到的表的实际名称
GROUP BY
    Product,
    [Period 1 start],
    [Period 1 end],
    [Period 2 start],
    [Period 2 end]

逻辑说明 #

• 分组维度选择Product和四个周期起止字段，你现有数据中同产品的周期参数完全一致，分组后不会出现冗余行
• 用CASE WHEN判断每行的销售日期是否落在对应周期区间内，符合条件则累加对应的销售金额/销量，不符合则加0，避免返回NULL值
• 如果你的业务规则中周期区间为左闭右开（即不包含结束日期当天），把BETWEEN [Period X start] AND [Period X end]改为>= [Period X start] AND < [Period X end]即可调整判断逻辑。

如果后续需要扩展更多统计周期，可以把周期参数单独抽为配置表，关联后用动态SQL生成统计逻辑，避免重复写CASE语句。

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