select 产品,
    数量=sum(数量),
    箱号范围=case when max(箱号)=min(箱号) then convert(varchar(20),max(箱号))
        else convert(varchar(20),min(箱号))+'-'+convert(varchar(20),max(箱号)) end,
    箱数=max(箱号)-min(箱号)+1
from B_HP
group by 产品
order by 产品

------解决方案--------------------

SQL code
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([产品] varchar(1),[数量] int,[箱号] int)
insert [tb]
select 'A',2,1 union all
select 'A',2,2 union all
select 'A',2,3 union all
select 'B',1,4 union all
select 'B',1,4 union all
select 'C',3,5 union all
select 'C',3,6
go

select 产品,
  sum(数量) as 数量,
  箱号范围=case when min(箱号)=max(箱号) then ltrim(min(箱号)) else ltrim(min(箱号))+'-'+ltrim(max(箱号)) end,
  count(distinct 箱号) as 箱数
from tb
group by 产品
/**
产品   数量          箱号范围                      箱数
---- ----------- ------------------------- -----------
A    6           1-3                       3
B    2           4                         1
C    6           5-6                       2

(3 行受影响)
**/