试试这个。对于你的数据库，用你的表名替换@t。

--create temp table for testing
declare @t table
(
    strata int,
    frequency int
)

--insert values into temp table
insert into @t (strata, frequency) values
(1,4),(2,6),(3,1),(4,1),(5,10)

--intermediate table for final strata values
declare @tmp table
(
    s int
)

--final strata values are those with frequency bigger than/equal to 2
insert into @tmp (s) 
(
    select strata 
    from @t
    where frequency>=2
)

select 
normalisedStrata as strata, 
sum(frequency) as frequency 

from
(
    select 
    --normalise the strata by testing frequency
    case 
        when frequency<2 
        --if freq<2, get biggest element of temp table that's less than the row's strata
        then (select max(s) from @tmp where s<strata) 
        --otherwise just copy across
        else strata 
    end as normalisedStrata, 
    frequency
from @t
--subquery must be named, just put arbitrary string
) a
group by normalisedStrata

您可以这样做。使用case表达式检查Frequency，返回1或0作为指示符。对其执行累积求和以形成grp。

with 
cte1 as
(
    select *, f = case when Frequency > 1 then 1 else 0 end
    from   strata
),
cte2 as
(
    select *, grp = sum(f) over (order by Strata)
    from   cte1
)
select Strata = min(Strata), Frequency = sum(Frequency)
from   cte2
group by grp
order by Strata