SQL Server按值计算连续行数

Question

这个问题对我来说很难写出来。

我有一个表，如下所示:

<表类> 日期 ItemNumber FlagA FlagB tbody><<tr>2020-01-01101YN2020-01-02101NN2020-01-03101YN2020-01-04101YN2020-01-05101YY2020-01-01102YN2020-01-02102NN2020-01-03102NN2020-01-04102YY

您似乎需要flagA = Y的最近不间断序列的枚举。那就是:

select t.*,
(case when grp = 0 and flagA = 'Y'
then row_number() over (partition by grp, flagA order by date) 
else 0
end) as runningCount
from (select t.*,
sum(case when flagA = 'N' then 1 else 0 end) over (partition by itemNumber order by date desc) as grp
from t
) t;