字段数据范围段无序重分组排列

minID            maxID
A0300000000020 A0300000000039
A0300000000001 A0300000000100

我现在分组之后显示的是上面的例子。我希望得到的是下面的样子

minID            maxID
A0300000000001 A0300000000020
A0300000000021 A0300000000039
A0300000000040 A0300000000100

 

 

szx2000--(sql2005)：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([grp] int,[id] varchar(14))
insert [tb]
select 1,'A0300000000001' union all
select 1,'A0300000000100' union all
select 2,'A0300000000020' union all
select 2,'A0300000000039'
go
--select * from [tb]

select minID=min(id),maxID=max(id)
from
(
    select id,rn=row_number() over(order by id)
    from
    (
        select id=left(id,3)+right(replicate('0',10)+rtrim(right(id,11)+1),11)
        from (select id,rn=row_number() over(order by id) from tb) t
        where rn>1 and id<(select max(id) from tb)
        union all
        select id from tbgroup by (rn-1)/2
/*
minID                        maxID
---------------------------- ----------------------------
A0300000000001               A0300000000020
A0300000000021               A0300000000039
A0300000000040               A0300000000100

(3 行受影响)
*/

) tt ) ttt

 

 

--libin(SQL2000)

 

declare @t table(minID varchar(20),maxID varchar(20))
insert into @t select 'A0300000000020','A0300000000039'
insert into @t select 'A0300000000001','A0300000000100'
insert into @t select 'B0300000000024','B0300000000059'
insert into @t select 'B0300000000074','B0300000000089'
insert into @t select 'B0300000000001','B0300000000100'

select
    a.minID,min(b.maxID) as maxID
from
    (select minID from @t
     union
     select left(maxID,1)+right(100000000000000+1+stuff(maxID,1,1,''),13)
     from @t t where exists(select 1 from @t where minID<t.minID and maxID>t.minID)) a,
    (select maxID from @t
     union
     select left(minID,1)+right(100000000000000-1+stuff(minID,1,1,''),13)
     from @t t where exists(select 1 from @t where minID<t.minID and maxID>t.minID)) b
where
    a.minID<b.maxID
group by
    a.minID

/*
minID                        maxID                       
---------------------------- ----------------------------
A0300000000001               A0300000000019
A0300000000020               A0300000000039
A0300000000040               A0300000000100
B0300000000001               B0300000000023
B0300000000024               B0300000000059
B0300000000060               B0300000000073
B0300000000074               B0300000000089
B0300000000090               B0300000000100
*/