如何通过SQL实现每到7月1日递增行号的ROW_NUMBER()用法

你之前的SQL计算逻辑存在本质偏差：

• 用CASE WHEN RIGHT(a.[test_date],5) = '07-01'做分区键，会把全量数据切成两个互不关联的分区：所有匹配07-01的行单独为一个分区，其余非07-01的行合并为另一个分区
• ROW_NUMBER()在每个分区内独立排序计数，算出来的是分区内部的行号，根本不是“遇到一次07-01就给分段值加1”的跨序列累计逻辑，自然得不到你要的分段固定值效果。

核心思路非常简单：逐行累计统计从日期序列起点到当前行，一共出现了多少次7月1日，累计的总次数就是当前行所属的分段编号。 #

带窗口函数的通用实现（适用于SQL Server/MySQL 8.0+/PostgreSQL等主流现代数据库） #

不需要多层嵌套，直接用带累计语义的窗口函数即可：

SELECT
    a.test_date,
    SUM(
        CASE 
            WHEN RIGHT(a.[test_date],5) = '07-01' THEN 1 
            ELSE 0 
        END
    ) OVER (ORDER BY a.[test_date]) AS row_num
FROM 你的源数据表 a
ORDER BY a.test_date

计算过程完全匹配你的预期分段：

1. 2018-07-01是序列第一个日期，标记为1，累计值为1；后续从2018-07-02到2019-06-30的所有行，标记均为0，累计值始终保持1
2. 遍历到2019-07-01时标记触发，累计值增加为2；后续直到2020-06-30的行累计值保持2不变
3. 遍历到2020-07-01时标记再次触发，累计值变为3

注：上述写法不需要额外声明窗口行范围——所有主流数据库中，带ORDER BY的聚合窗口，默认统计范围就是从序列起始行到当前行。 #

旧版本数据库兼容实现（适用于无窗口函数的环境，比如MySQL 5.x） #

用会话变量逐行累计，效果和窗口函数完全一致：

SELECT
    a.test_date,
    -- 碰到07-01就给分段计数加1，否则沿用当前值
    @seg_cnt := @seg_cnt + IF(RIGHT(a.test_date,5) = '07-01', 1, 0) AS row_num
FROM 你的源数据表 a,
     -- 初始化分段计数从0开始
     (SELECT @seg_cnt := 0) init_param
ORDER BY a.test_date

内容的提问来源于stack exchange，提问作者Chicken Sandwich No Pickles