SQL Server中实现累计达标后转下一行的需求方案咨询

核心逻辑说明 #

我们需要将用户的积分/晚数记录按等级阈值逐步拆分：优先凑够当前等级的达标要求，剩余的记录再累加到下一个等级，直到所有记录分配完毕。

步骤1：构建等级阈值数据集 #

先把固定的等级阈值做成CTE（临时数据集），避免硬编码，方便后续关联：

WITH LevelThresholds AS (
    SELECT 
        mrid = 63, 
        level_name = '银级',
        point_threshold = 10000,
        night_threshold = 10
    UNION ALL
    SELECT 64, '金级', 20000, 20
    UNION ALL
    SELECT 65, '铂金级', 40000, 30
),

步骤2：计算用户记录的累计值 #

按用户分组，按记录产生顺序（假设aid是记录先后顺序标识，可替换为实际时间字段）计算积分和晚数的实时累计值：

UserRunningTotals AS (
    SELECT 
        mid,
        aid,
        ctid,
        ctv,
        -- 累计积分：仅统计ctid=5813的记录
        running_points = SUM(CASE WHEN ctid = 5813 THEN ctv ELSE 0 END) 
                         OVER (PARTITION BY mid ORDER BY aid ROWS UNBOUNDED PRECEDING),
        -- 累计晚数：仅统计ctid=5817的记录
        running_nights = SUM(CASE WHEN ctid = 5817 THEN ctv ELSE 0 END) 
                         OVER (PARTITION BY mid ORDER BY aid ROWS UNBOUNDED PRECEDING)
    FROM 表2
),

步骤3：关联用户等级，计算各等级缺口 #

将用户等级信息（表1）与阈值、累计记录关联，计算每个等级还需多少积分/晚数才能达标：

UserLevelGaps AS (
    SELECT 
        urt.mid,
        lt.mrid,
        lt.level_name,
        lt.point_threshold,
        lt.night_threshold,
        urt.aid,
        urt.ctid,
        urt.ctv,
        urt.running_points,
        urt.running_nights,
        -- 积分缺口：当前累计未达标时，离阈值的差值
        point_needed = IIF(urt.running_points < lt.point_threshold, 
                           lt.point_threshold - (urt.running_points - urt.ctv), 0),
        -- 晚数缺口：同理计算晚数达标剩余量
        night_needed = IIF(urt.running_nights < lt.night_threshold, 
                           lt.night_threshold - (urt.running_nights - urt.ctv), 0)
    FROM UserRunningTotals urt
    JOIN 表1 u ON urt.mid = u.mid
    JOIN LevelThresholds lt ON lt.mrid >= u.mrid -- 关联用户当前及更高等级，按需调整
),

步骤4：拆分记录到对应等级 #

判断每条记录中，有多少量用于当前等级达标，剩余部分留给下一个等级：

FinalSplit AS (
    SELECT 
        mid,
        mrid,
        level_name,
        aid,
        ctid,
        -- 计算当前记录贡献给当前等级的量：累计前未达标则取记录值与缺口的较小值，否则为0
        used_value = CASE 
            WHEN ctid = 5813 THEN IIF(point_needed > 0, IIF(ctv < point_needed, ctv, point_needed), 0)
            WHEN ctid = 5817 THEN IIF(night_needed > 0, IIF(ctv < night_needed, ctv, night_needed), 0)
            ELSE 0
        END,
        -- 剩余未使用的量，转入下一等级计算
        remaining_value = ctv - used_value
    FROM UserLevelGaps
)

最终查询示例 #

按用户+等级分组，查看每个等级累计的达标贡献量：

SELECT 
    mid,
    level_name,
    SUM(used_value) AS total_contributed,
    -- 显示该等级最终的累计完成值
    MAX(CASE WHEN ctid=5813 THEN running_points ELSE running_nights END) AS final_accumulation
FROM FinalSplit
WHERE used_value > 0
GROUP BY mid, level_name
ORDER BY mid, mrid;

关键注意事项 #

1. 排序字段：如果aid不是记录的顺序依据，需替换为实际时间字段（如create_time），否则累计顺序会出错。
2. 动态等级：若用户等级随时间变化，需给表1添加等级生效/失效时间，关联时过滤对应时间段的等级数据。
3. 等级范围调整：如果仅需处理用户当前等级的达标，将lt.mrid >= u.mrid改为lt.mrid = u.mrid即可。

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