不使用ROW_NUMBER与Partition By实现分组自增序号（值变重置）

需求说明 #

需要创建一个自增的序号字段（RowNumb），当Meal字段的值发生变化时，序号自动重置为1。目标结果如下：

尝试的错误代码 #

尝试了以下SQL，但序号未在Meal变化时重置，而是持续递增：

SELECT
    t1.meal,
    COUNT(t2.meal) + 1 AS RowNumb
FROM CTE t1
JOIN CTE t2 ON t2.time < t1.time OR (t2.time = t1.time AND t2.meal <> t1.meal)
GROUP BY
    t1.time,
    t1.meal
ORDER BY
    t1.time;

错误结果 #

执行上述代码后得到的不符合预期的结果：

正确解决方案（禁用ROW_NUMBER/PARTITION BY） #

可以通过关联子查询实现，核心是限定只统计当前Meal分组内时间早于当前记录的行数，以此实现分组内自增、切换分组重置的效果：

SELECT
    t1.meal,
    t1.time,
    (SELECT COUNT(*) + 1 
     FROM CTE t2 
     WHERE t2.meal = t1.meal 
       AND t2.time < t1.time) AS RowNumb
FROM CTE t1
ORDER BY t1.time;

补充说明 #

如果存在同一Meal下时间相同的记录，需要按额外字段排序的话，可在子查询的WHERE条件中添加排序字段，比如假设存在唯一id字段：

(SELECT COUNT(*) + 1 
 FROM CTE t2 
 WHERE t2.meal = t1.meal 
   AND (t2.time < t1.time OR (t2.time = t1.time AND t2.id < t1.id))) AS RowNumb

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