/******************************************************************************/ 
 /*回复：20080519104总：00024                           */ 
 /*主题：合并记录                                     */ 
 /*作者：二等草                                       */ 
 /******************************************************************************/ 
  
 set nocount on 
  
 --数据-------------------------------------- 
 create table [A] ([A] varchar(2),[B] varchar(10),[C] varchar(10),[D] int) 
  insert into [A] select 'aa','ssd0000001','ssd0000005',5 
  insert into [A] select 'aa','ssd0000006','ssd0000020',15 
  insert into [A] select 'aa','ssd0000022','ssd0000030',8 
  insert into [A] select 'bb','ss000100','ss000500',400 
  insert into [A] select 'bb','ss000501','ss000509',9 
 go 
 --代码-------------------------------------- 
 create function getv(@d varchar(10)) 
 returns int 
 as 
 begin  
  declare @i int 
  set @i = right(@d,patindex('%[^0-9]%',reverse(@d))-1) 
  return @i 
 end 
 go 
 create function getmaxend(@a varchar(2),@b varchar(10)) 
 returns varchar(20) 
 as 
 begin 
  declare @i int,@c varchar(10) 
  if exists(select 1 from a where @a = a and dbo.getv(@b)-dbo.getv(c)=1) return '' 
  select @i = d,@c = c from a where @a =a and @b = b 
  while exists(select 1 from a where @a = a and dbo.getv(b)-dbo.getv(@c)=1) 
  select @i = @i+d,@c = c from a where @a = a and dbo.getv(b)-dbo.getv(@c) =1 
  return @c+','+rtrim(@i) 
 end 
 go 
 select a,b,c= left(c,charindex(',',c)-1),d=right(c,len(c)-charindex(',',c)) from ( 
 select a,b,c=dbo.getmaxend(a,b) from a where dbo.getmaxend(a,b) <> '') a 
 go 
 /*结果-------------------------------------- 
 a  b      c          d           
 ---- ---------- -------------------- --------------------  
 aa   ssd0000001 ssd0000020       20 
 aa   ssd0000022 ssd0000030       8 
 bb   ss000100   ss000509       409 
  
 --清除------------------------------------*/ 
 drop function getv,getmaxend 
 go 
 drop table a