select id,min(序号) as 起号, max(序号) as 止号 from tablename group by id
create table #t(id varchar(10),序号 int) insert into #t select 'a',1 union all select 'a',2 union all select 'a',3 union all select 'b',4 union all select 'b',5 union all select 'c',6 select id,min(序号) as 起号,max(序号) as 止号 from #t group by id/* id 起号 止号 ---------- ----------- ----------- a 1 3 b 4 5 c 6 6(所影响的行数为 3 行) */
select * from ta a where not exists(select 1 from ta where id=a.id and 序号>a.序号)
insert into #t
select 'a',1
union all select 'a',2
union all select 'a',3
union all select 'b',4
union all select 'b',5
union all select 'c',6
select id,min(序号) as 起号,max(序号) as 止号
from #t
group by id/*
id 起号 止号
---------- ----------- -----------
a 1 3
b 4 5
c 6 6(所影响的行数为 3 行)
*/
where not exists(select 1 from ta where id=a.id and 序号>a.序号)