具有select [item],[statusid],count(*) occurence from ( select *,lag([item]) over (order by (select null)) previtem,lag([statusid]) over (order by (select null)) prevstatusid from tablename ) t where [item] <> previtem or [statusid] <> prevstatusid or (previtem is null and prevstatusid is null) group by [item],[statusid] 窗口功能：

> item   | statusid | occurence
> :----- | :------- | --------:
> pencil | A        |         3
> pencil | B        |         2
> pencil | C        |         1
> eraser | D        |         2
> eraser | E        |         2
> eraser | F        |         2

请参见demo。
结果：

Process.Kill()

注意：检查@SeanLange的评论之一中的链接。
此代码适用于您发布的样本数据以及相对较小的表，但不适用于非常大的表。
无论如何，您都需要一个确定行顺序的列。

with cte as
 (
   select item,statusid,-- tag consecutive rows with the same status
      case when lag(statusid) 
                over (partition by item
                      order by whatever) = statusid -- same status
           then 0 -- remove or
           else 1 -- keep in next step
      end as flag
   from mytable
 )
select item,count(*)
from cte
where flag = 1 -- now remove those rows
group by item,statusid