这是一个间隙和孤岛的问题，但是我将使用行数差异方法：
select orderid, count(*), min(serialNumber), max(serialNumber), type
from (select t.*,
row_number() over (partition by orderid order by serialnumber) as seqnum,
row_number() over (partition by orderid, type order by serialnumber) as seqnum_type
from t
) t
group by orderid, type, (seqnum - seqnum_type)
order by orderid, min(serialNumber);
您serialnumber看起来像一个字符串，但它具有数字值。如果您可以信任它的排序，那么您甚至都不需要两个行号值：
select orderid, count(*), min(serialNumber), max(serialNumber), type
from (select t.*,
row_number() over (partition by orderid, type order by serialnumber) as seqnum_type
from t
) t
group by orderid, type, (serialnumber - seqnum_type)
order by orderid, min(serialNumber);
起作用的原因有点难以解释，但是如果运行子查询，则很明显。您将看到行号之间的差异如何标识您要标识的组。
清洁得多...我只是将Flag方法卡在了我的小脑袋+1中