我相信这就是你想要的。我用 LAG 获取cte中前一行的值，然后使用窗口 COUNT 降低…的价值 ROW_NUMBER 对于具有相同连续值的每行，按1 amount :

WITH CTE AS(
    SELECT Area,
           DateA,
           DateB,
           Amount,
           LAG(Amount) OVER (PARTITION BY Area ORDER BY DateA) AS PrevAmount
    FROM (VALUES
        ('ABC', '2019-08-18', '2019-08-18 00:07:47.000', 3.75),
        ('ABC','2019-08-19', '2019-08-19 00:08:47.000', 3.75),
        ('ABC','2019-08-20', '2019-08-20 00:09:47.000', 3.65),
        ('ABC','2019-08-21', '2019-08-21 00:09:57.000', 3.75))
        AS FeeCollection(Area, DateA, DateB, Amount))
SELECT Area,
       DateA,
       DateB,
       Amount,
       ROW_NUMBER() OVER (PARTITION BY Area ORDER BY DateA) - 
       COUNT(CASE Amount WHEN PrevAmount THEN 1 END) OVER (PARTITION BY Area ORDER BY DateA
                                                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Number
FROM CTE
ORDER BY DateA;

我确实认为你 PARTITION BY 条款，您可能需要更改/删除/移动到 ORDER BY . 因为我们只有一个价值 Area 当它改变时，不可能知道它的值应该是什么。

我会用 lag() 一个累积的总和，但看起来像：

select t.*,
       sum(case when prev_amount = amount then 0 else 1 end) over
           (partition by area order by datea) as number
from (select t.*,
             lag(amount) over (partition by area order by datea) as prev_amount
      from t
     ) t;