如何根据日期为指定Id和Code自动更新表中Days字段值

需要更新summry表的Days字段，计算规则如下：

• 按id和code分组
• 分组内按日期排序，若当前日期与前一条日期连续（数值差为1），则Days在上一条基础上加1
• 若日期中断（与前一条日期数值差超过1），则Days重置为1

表结构 #

create table summry(
  id  varchar(12),
  `Desc` varchar(50),
  Qty decimal(25),
  code varchar(20),
  Date int,
  Days int
);

注：Desc为SQL关键字，需用反引号包裹避免语法冲突

现有数据 #

预期更新后的数据 #

注：因14日无数据，15日的Days需重置为1

SQL更新语句 #

方法1：支持窗口函数的数据库（MySQL 8.0+、SQL Server、PostgreSQL等） #

通过窗口函数识别连续日期分组，再在组内生成递增序号：

WITH ranked_data AS (
    SELECT 
        id,
        code,
        Date,
        -- 生成连续日期分组ID，日期中断时分组ID递增
        SUM(CASE WHEN Date = LAG(Date) OVER (PARTITION BY id, code ORDER BY Date) + 1 THEN 0 ELSE 1 END) 
            OVER (PARTITION BY id, code ORDER BY Date) AS group_id,
        -- 组内按日期生成递增序号，即为目标Days值
        ROW_NUMBER() OVER (PARTITION BY id, code, group_id ORDER BY Date) AS new_days
    FROM summry
)
UPDATE summry s
JOIN ranked_data rd ON s.id = rd.id AND s.code = rd.code AND s.Date = rd.Date
SET s.Days = rd.new_days;

方法2：不支持窗口函数的数据库（如MySQL 5.x） #

使用变量记录分组状态，逐行计算Days值：

UPDATE summry s
JOIN (
    SELECT 
        id,
        code,
        Date,
        @current_group := CASE 
            WHEN @prev_id = id AND @prev_code = code AND Date = @prev_date + 1 THEN @current_group 
            ELSE @current_group + 1 
        END AS group_id,
        @row_num := CASE 
            WHEN @prev_id = id AND @prev_code = code AND @current_group = @prev_group THEN @row_num + 1 
            ELSE 1 
        END AS new_days,
        -- 更新变量缓存
        @prev_id := id,
        @prev_code := code,
        @prev_date := Date,
        @prev_group := @current_group
    FROM summry,
         (SELECT @prev_id := '', @prev_code := '', @prev_date := 0, @current_group := 0, @row_num := 0) vars
    ORDER BY id, code, Date
) rd ON s.id = rd.id AND s.code = rd.code AND s.Date = rd.Date
SET s.Days = rd.new_days;

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