SQL多表关联时如何按指定日期区间计算分段价格总和？

核心实现思路 #

• 先单独聚合价格表数据，避免和媒体、详情表关联时因一对多关系导致价格重复计算
• 用区间重叠逻辑筛选符合查询时段的价格记录：价格段起始日≤查询截止日、价格段截止日≥查询起始日即判定为有重叠
• 计算重叠时段的计费天数：取两个时段的较晚起始日、较早计费截止日，按天计算差值后+1（包含首尾计费日），乘以对应单日价格后求和
• 计费规则匹配给出的示例：传入的@To为离店日不计费，实际计费截止日为@To往前推1天

完整SQL代码 #

-- 存储过程参数
@From as date,
@To as date

WITH UnitPeriodPrice AS (
    SELECT
        p.UnitId,
        SUM(
            p.Price * (
                DATEDIFF(
                    day,
                    -- 重叠计费起始日：取价格段起始和查询起始的较晚值
                    CASE WHEN p.[From] > @From THEN p.[From] ELSE @From END,
                    -- 重叠计费截止日：取价格段截止和查询计费截止（离店日-1）的较早值
                    CASE WHEN p.[To] < DATEADD(day, -1, @To) THEN p.[To] ELSE DATEADD(day, -1, @To) END
                ) + 1 -- 日期差+1，包含首尾两个计费日
            )
        ) AS TotalPrice
    FROM Prices p
    WHERE
        -- 筛选和查询时段存在重叠的价格记录
        p.[From] <= @To
        AND p.[To] >= @From
    GROUP BY p.UnitId
)
SELECT
    u.Id,
    u.Description AS UnitDescription,
    d.Description AS DetailDescription,
    m.path AS MediaPath,
    ISNULL(upp.TotalPrice, 0) AS PeriodTotalPrice
FROM Unit u -- 若主表实际名为Rooms，替换此处表名即可
LEFT JOIN Details d
    ON u.Id = d.UnitId
LEFT JOIN Medias m
    ON u.Id = m.UnitId
LEFT JOIN UnitPeriodPrice upp
    ON u.Id = upp.UnitId

示例参数验证 #

传入参数：@From = '2022-07-07'、@To = '2022-07-11'，计算结果完全匹配预期：

• 单元1855总价：
  • 价格段2022-06-18~2022-07-08重叠计费2天（7.7、7.8）：203.86 * 2 = 407.72
  • 价格段2022-07-09~2022-08-12重叠计费2天（7.9、7.10）：128.57 * 2 = 257.14
  • 合计：407.72 + 257.14 = 664.86
• 单元2600总价：
  • 价格段2022-06-18~2022-07-08重叠计费2天（7.7、7.8）：231.86 * 2 = 463.72
  • 价格段2022-07-09~2022-08-12重叠计费2天（7.9、7.10）：322.57 * 2 = 645.14
  • 合计：463.72 + 645.14 = 1108.86

注意事项 #

• 如果业务规则中@To需要包含在计费时段内（即不是离店日），将代码中DATEADD(day, -1, @To)全部替换为@To即可
• 如果单个单元对应多条媒体、详情记录，当前查询会返回多行（每行对应一个媒体/详情）；如果需要每个单元仅返回一行，可先对Details、Medias表按UnitId做聚合（如取首个媒体路径、拼接所有详情）后再关联
• 若查询时段内某单元无匹配价格记录，PeriodTotalPrice会返回0，如需返回NULL可去掉ISNULL函数

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