MySQL中如何基于CASE WHEN逻辑正确实现自定义变量的条件自增

你遇到的逐行计数器无条件自增的问题，本质是MySQL的执行规则导致的计算顺序错位：

• 窗口函数是批量并行计算的，不属于逐行流处理逻辑，计算优先级高于SELECT列表里的普通表达式
• 当你在同一个SELECT层级同时写窗口函数、CASE判断、用户变量自增逻辑时，优化器不会按照你预期的「逐行取上一行End_Date → 算diff → 判断diff值 → 决定是否给count自增」的顺序执行，反而会打乱表达式的执行顺序，提前触发ELSE分支的自增赋值，最终出现每一行count都无条件+1的结果。

要实现你要的逻辑，必须把「窗口函数批量计算」和「用户变量逐行条件自增」拆成两个独立的执行层级：

1. 内层先通过子查询完成所有窗口函数的计算，把prev、diff的结果固化成带固定排序的结果集
2. 外层再基于固化好的逐行结果，执行CASE判断和变量条件更新，此时外层的逐行处理顺序完全受控，不会出现计算错位的问题。

-- 初始化计数器为0，匹配第一行的预期输出
SET @count := 0;

SELECT
  task_id,
  start_date,
  end_date,
  prev,
  diff,
  CASE
    -- 第一行（diff为NULL）不改变计数器值，直接返回当前值
    WHEN diff IS NULL THEN @count
    -- 相邻日期差为0时不改变计数器值，直接返回当前值
    WHEN diff = 0 THEN @count
    -- 仅当日期差非空且不为0时，计数器自增1并返回新值
    ELSE @count := @count + 1
  END AS cnt
FROM (
  -- 内层子查询：批量完成窗口计算，固定排序
  SELECT
    task_id,
    start_date,
    end_date,
    LAG(end_date) OVER(ORDER BY start_date) AS prev,
    DATEDIFF(LAG(end_date) OVER(ORDER BY start_date), start_date) AS diff
  FROM projects
  ORDER BY start_date
) AS sorted_tasks
ORDER BY start_date;

• 必须在子查询内显式指定ORDER BY排序，保证外层逐行处理的顺序和你业务上定义的相邻行规则一致，否则变量计算结果会因行序错乱完全不符合预期
• 不要把窗口函数、用户变量赋值放在同一个SELECT层级，这是MySQL自定义变量做行级累积统计的通用踩坑点，拆层是最稳定可控的实现方式

运行以上SQL即可得到你预期的输出：前4行因为diff连续为0，cnt保持0；当出现非0的diff（2015-10-11那行）时cnt变为1，后续连续diff为0的行cnt保持1，遇到下一个非0 diff时再继续累加。

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