求基于输入日期范围与双表计算每日费率的T-SQL查询方案

没问题，我来帮你搞定这个需求！首先我先基于常见的业务场景假设两张表的结构（如果你的实际表结构有差异，调整字段名和关联逻辑就行）：

假设的表结构 #

费率表（RateTable） #

这张表存储不同时间段的日费率，支持多条记录：

CREATE TABLE RateTable (
    RateID INT PRIMARY KEY,
    EffectiveStartDate DATE NOT NULL, -- 费率生效起始日期
    EffectiveEndDate DATE NOT NULL, -- 费率生效结束日期
    DailyRate DECIMAL(10,2) NOT NULL -- 每日费率值
);

输入日期范围 #

我们用两个变量来接收输入的起止日期（如果你的日期范围存在单独的表中，直接替换成表查询即可）：

DECLARE @StartDate DATE = '2024-01-01';
DECLARE @EndDate DATE = '2024-01-31';

核心查询代码 #

情况1：有现成的日期维度表 #

如果你的数据库里有一张包含所有日期的维度表（比如DateDimension，包含Date字段），直接关联即可：

-- 生成每日费率明细，同时计算总计
WITH DailyRates AS (
    SELECT
        dd.Date AS CalculationDate,
        rt.DailyRate
    FROM
        DateDimension dd
    LEFT JOIN
        RateTable rt ON dd.Date BETWEEN rt.EffectiveStartDate AND rt.EffectiveEndDate
    WHERE
        dd.Date BETWEEN @StartDate AND @EndDate
)
SELECT
    CalculationDate,
    DailyRate,
    SUM(DailyRate) OVER () AS TotalRate -- 总费率求和
FROM
    DailyRates
ORDER BY
    CalculationDate;

情况2：无日期维度表，用CTE生成连续日期 #

如果没有现成的日期表，我们可以用递归CTE生成输入范围内的所有连续日期：

-- 递归CTE生成连续日期
WITH DateSequence AS (
    SELECT @StartDate AS CalculationDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CalculationDate)
    FROM DateSequence
    WHERE CalculationDate < @EndDate
),
-- 匹配每日对应的费率
DailyRates AS (
    SELECT
        ds.CalculationDate,
        COALESCE(rt.DailyRate, 0) AS DailyRate -- 如果某天没有匹配的费率，默认0
    FROM
        DateSequence ds
    LEFT JOIN
        RateTable rt ON ds.CalculationDate BETWEEN rt.EffectiveStartDate AND rt.EffectiveEndDate
)
-- 输出明细及总计
SELECT
    CalculationDate,
    DailyRate,
    SUM(DailyRate) OVER () AS TotalRate
FROM
    DailyRates
ORDER BY
    CalculationDate
OPTION (MAXRECURSION 0); -- 解决递归CTE的默认递归次数限制（如果日期范围超过100天需要加这个）

关键注意事项 #

• 费率重叠处理：如果你的费率表存在时间段重叠的记录，需要额外逻辑（比如取EffectiveStartDate最新的费率，或者按优先级字段筛选），可以在LEFT JOIN后加ROW_NUMBER()来排序取有效费率。
• 边界日期：如果你的EffectiveEndDate是开区间（比如费率到2024-01-10结束，意味着2024-01-10不再生效），把BETWEEN改成ds.CalculationDate >= rt.EffectiveStartDate AND ds.CalculationDate < rt.EffectiveEndDate。
• 空费率处理：用COALESCE把无匹配的费率设为0，避免结果出现NULL。

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