如何在MySQL中从滚动计数查询得到实际累计计数

方案1：查询时动态计算（推荐，无需修改表结构/触发器） #

针对支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server等），可以直接在查询时通过统计复位次数计算实际累计值，逻辑完全匹配你给出的示例输出：

SELECT 
  Dev_count,
  SUM(CASE WHEN Dev_count = 5 THEN 1 ELSE 0 END) 
    OVER (PARTITION BY device_id ORDER BY No ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) * 6 
  + Dev_count AS Actual_Count
FROM x_table
ORDER BY No;

逻辑说明： #

• 每次Dev_count走到5说明即将复位，统计当前行之前的复位次数（即Dev_count=5的出现次数）
• 每个复位周期对应6个计数，因此复位次数乘以6加上当前Dev_count值，就是实际累计计数

方案2：旧版本无窗口函数数据库适配 #

如果使用不支持窗口函数的低版本MySQL，可以用用户变量实现相同逻辑：

SELECT 
  Dev_count,
  @reset_times * 6 + Dev_count AS Actual_Count
FROM (
  SELECT 
    Dev_count,
    @reset_times := @reset_times + IF(@prev_dev = 5, 1, 0),
    @prev_dev := Dev_count
  FROM x_table 
  ORDER BY No
) t,
(SELECT @reset_times := 0, @prev_dev := -1) init_vars;

方案3：修正触发器实现持久化存储 #

如果需要把实际计数持久化存储到表中，可以修正触发器写法规避同表操作报错：

DELIMITER //
CREATE TRIGGER insert_continuous_count BEFORE INSERT ON x_table
FOR EACH ROW
BEGIN
  DECLARE last_continuous INT DEFAULT 0;
  SELECT IFNULL(continuous_count, 0) INTO last_continuous
  FROM x_table
  WHERE device_id = NEW.device_id
  ORDER BY No DESC LIMIT 1 FOR UPDATE;
  SET NEW.continuous_count = last_continuous + 1;
END //
DELIMITER ;

注意事项： #

加FOR UPDATE是为了规避并发插入时的计数重复问题，该写法在绝大多数MySQL版本下可以绕过同表读写限制。如果仍有报错，优先使用方案1的动态查询逻辑，无需维护触发器，无并发死锁风险。

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