SQL如何在指定日期范围内使用sum()函数计算周期价格总和

核心逻辑说明 #

• 价格计算规则：取价格表记录和入参查询时间段的重叠有效天数，乘以对应单日价格后按单元聚合求和，避免非整段覆盖场景的计算错误。
• 防重复计算：由于Details、Medias表可能存在一个单元对应多条记录的情况，先单独聚合每个单元的周期总价，再关联回主查询，避免多表一对多关联产生笛卡尔积导致金额累加错误。
• 有效区间判断：仅保留和查询时间段存在重叠的价格记录，提前过滤无效数据提升查询效率。

可直接运行的SQL代码 #

-- 存储过程入参
@From AS DATE,
@To AS DATE

;WITH UnitPeriodPrice AS (
    SELECT
        p.UnitId,
        SUM(
            p.Price * (
                DATEDIFF(
                    day,
                    -- 重叠区间起始：取价格生效日、查询起始日的较晚值
                    GREATEST(p.[From], @From),
                    -- 重叠区间结束：取价格失效日、查询截止日（离店日不计费，故往前推1天）的较早值
                    LEAST(p.[To], DATEADD(day, -1, @To))
                ) + 1 -- +1用于包含区间首尾日期，符合按入住夜数统计的业务规则
            )
        ) AS TotalPrice
    FROM Prices p
    WHERE
        p.[From] <= DATEADD(day, -1, @To)
        AND p.[To] >= @From
    GROUP BY p.UnitId
)

SELECT  
    Unit.Id,
    Unit.Description,
    Details.Description,
    Medias.path,
    ISNULL(upp.TotalPrice, 0) AS PeriodTotalPrice
FROM Unit
LEFT JOIN Details
    ON Unit.Id = Details.UnitId
LEFT JOIN Medias
    ON Unit.Id = Medias.UnitId
LEFT JOIN UnitPeriodPrice upp
    ON Unit.Id = upp.UnitId

结果验证 #

传入@From = '2022-07-07'、@To = '2022-07-11'时：

• 单元1855的有效计费天数为4天：7月7、8日执行203.86单价，7月9、10日执行128.57单价，总价为2*203.86 + 2*128.57 = 664.86
• 单元2600的有效计费天数为4天：7月7、8日执行231.86单价，7月9、10日执行322.57单价，总价为2*231.86 + 2*322.57 = 1108.86
  和预期结果完全匹配。

适配说明 #

如果你的业务规则中价格表的To字段为计费截止日（即离店当天也需要计费），去掉代码中两处DATEADD(day, -1, @To)的日期偏移逻辑即可。

内容的提问来源于stack exchange，提问作者Simon Pietro Lentinello