--查询 select [count]=a.id2-a.id+1,a.name,sum(b.number) from( select id,name ,id2=( select min(id) from 表 aa where name=a.name and id>=a.id and not exists(select 1 from 表 where name=aa.name and id=aa.id+1) ) from 表 a where not exists(select 1 from 表 where name=a.name and id=a.id-1) )a join 表 b on a.name=b.name and b.id between a.id and a.id2 group by a.id,a.id2,a.name order by a.id
--测试--测试数据 create table 表(ID int,name varchar(10),number int) insert 表 select 1,'Q213E',3 union all select 2,'Q213E',5 union all select 3,'R136F',2 union all select 4,'R136F',3 union all select 5,'R136F',1 union all select 6,'Q213E',3 union all select 7,'Q213E',2 union all select 8,'R136F',6 union all select 9,'R136F',2 go--查询 select [count]=a.id2-a.id+1,a.name,number=sum(b.number) from( select id,name ,id2=( select min(id) from 表 aa where name=a.name and id>=a.id and not exists(select 1 from 表 where name=aa.name and id=aa.id+1) ) from 表 a where not exists(select 1 from 表 where name=a.name and id=a.id-1) )a join 表 b on a.name=b.name and b.id between a.id and a.id2 group by a.id,a.id2,a.name order by a.id go--删除测试 drop table 表/*--测试结果count name number ----------- ---------- ----------- 2 Q213E 8 3 R136F 6 2 Q213E 5 2 R136F 8(所影响的行数为 4 行) --*/
select [count]=a.id2-a.id+1,a.name,sum(b.number)
from(
select id,name
,id2=(
select min(id) from 表 aa
where name=a.name and id>=a.id
and not exists(select 1 from 表 where name=aa.name and id=aa.id+1)
)
from 表 a
where not exists(select 1 from 表 where name=a.name and id=a.id-1)
)a join 表 b on a.name=b.name and b.id between a.id and a.id2
group by a.id,a.id2,a.name
order by a.id
create table 表(ID int,name varchar(10),number int)
insert 表 select 1,'Q213E',3
union all select 2,'Q213E',5
union all select 3,'R136F',2
union all select 4,'R136F',3
union all select 5,'R136F',1
union all select 6,'Q213E',3
union all select 7,'Q213E',2
union all select 8,'R136F',6
union all select 9,'R136F',2
go--查询
select [count]=a.id2-a.id+1,a.name,number=sum(b.number)
from(
select id,name
,id2=(
select min(id) from 表 aa
where name=a.name and id>=a.id
and not exists(select 1 from 表 where name=aa.name and id=aa.id+1)
)
from 表 a
where not exists(select 1 from 表 where name=a.name and id=a.id-1)
)a join 表 b on a.name=b.name and b.id between a.id and a.id2
group by a.id,a.id2,a.name
order by a.id
go--删除测试
drop table 表/*--测试结果count name number
----------- ---------- -----------
2 Q213E 8
3 R136F 6
2 Q213E 5
2 R136F 8(所影响的行数为 4 行)
--*/