/******************************************************************************/ /*回复: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
/******************************************************************************/
/*回复: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