如何按值分组，但仅在它们是连续的时分组？

你有一个缺口和孤岛问题，一个选择是使用两个row_number之间的差异来定义所需的组，尝试使用SQL Server语法:

with cte1 as -- Step 1: using the difference between two row_numbers approach, create groups for consecutive similar values of Size.
(
select *,
row_number() over (partition by id order by number) - 
row_number() over (partition by id, size order by number) grp
from table_name 
),
cte2 as -- Step 2: get the counts for each group defined in the previous step
(
select id, min(number) number, size, grp, count(*) cnt
from cte1
group by id, size, grp
)
-- Step 3: use string_agg and concat functions to get the desired format
select id, 
string_agg
(-- use a case expression to not include (1x) when count = 1
case when cnt > 1 then concat(size, ' (x', cnt, ')') else cast(size as varchar(20)) end, ' x '
) within group (order by number) Size
from cte2
group by id
order by id

看到演示