基于两表多列匹配与累积求和的SQL计算列实现

核心逻辑拆解 #

明确计算规则：

• 按Table1每行的X+Y组合，匹配Table2中同维度的周数据
• 计算整周数：full_weeks = A // 7（取A除以7的整数部分）
• 计算剩余天数：remaining_days = A % 7（取A除以7的余数）
• B列值 = 前full_weeks个周的C值总和 + （剩余天数/7）× 第full_weeks+1个周的C值（仅剩余天数>0时生效）

通用SQL实现代码 #

WITH ranked_weeks AS (
    -- 给每个X,Y分组的周数据按日期排序，标记周序号
    SELECT 
        X,
        Y,
        Week,
        C,
        ROW_NUMBER() OVER (PARTITION BY X, Y ORDER BY Week) AS week_num
    FROM Table2
)
UPDATE Table1 t1
SET B = (
    -- 计算整周部分的C值总和
    COALESCE((
        SELECT SUM(C)
        FROM ranked_weeks rw
        WHERE rw.X = t1.X 
          AND rw.Y = t1.Y
          AND rw.week_num <= FLOOR(t1.A / 7)
    ), 0)
    -- 加上剩余天数对应的比例值
    + CASE 
        WHEN t1.A % 7 > 0 THEN 
            (t1.A % 7) / 7.0 * (
                SELECT C
                FROM ranked_weeks rw
                WHERE rw.X = t1.X 
                  AND rw.Y = t1.Y
                  AND rw.week_num = FLOOR(t1.A / 7) + 1
            )
        ELSE 0
    END
);

代码逐段解释 #

1. ranked_weeks 公共表表达式（CTE）

   • 用ROW_NUMBER()给每个X+Y组的周数据按日期排序，生成week_num（第1周、第2周...），确保能按时间顺序取周数据。

2. UPDATE 更新逻辑

   • 整周求和部分：用FLOOR(t1.A /7)得到整周数，筛选对应序号的周数据求和，COALESCE用来处理无匹配数据时返回0，避免空值错误。
   • 剩余天数比例部分：用CASE判断剩余天数是否大于0，若符合则取下一周的C值，乘以剩余天数/7.0（用7.0确保浮点数计算，避免整数除法丢失精度）。

注意事项 #

• 若你的SQL数据库不支持WITH语法（如老版本MySQL），可将ranked_weeks转换为子查询使用。
• 需确保Table2中X+Y+Week是唯一组合，避免重复数据导致排序错误。
• 若Table1某行的A对应的周数超过Table2中同X+Y组的周数，超出部分会被忽略（求和为0，剩余天数部分不计算），需特殊处理的话可额外添加判断逻辑。

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