一个表中记录的字段有:user_no(编号) 和(old_number电话号码)(不必关心号码的真实性,只要解决问题)
原始记录如:
user_no old_number
1 13729554450-4499
2 1398696450-99
3 137467861150-1199
4 13546214602-4605
现需将这些号码一一展开,如表中的第一条记录展开:
1 13729554450 13729554450-4499
1 13729554451 13729554450-4499
1 13729554452 13729554450-4499
. . .
. . .
1 13729554499 13729554450-4499
T-SQL实现如下:
Create Table #T(user_no int,old_number varchar(50))
insert #T(user_no,old_number) values(1 ,'13729554450-4499')
insert #T(user_no,old_number) values(2 ,'1398696450-99')
insert #T(user_no,old_number) values(3 ,'137467861150-1199')
go
Declare @t1 table(id int identity(1,1),userno varchar(30),oldnumber varchar(50))
insert into @t1
Select * from #T
Declare @t2 table(ID int,number varchar(30),OldNumber varchar(50))
Declare @i int,@Max int,@pos tinyint,@count int,@j bigint,@oldid int
Declare @oldnumber varchar(50),@number varchar(30),@newnumber varchar(30)
Select @i=Min(ID),@Max=Max(ID) from @t1
while @i<=@Max
begin
--代替游标迭代记录集,
Select @oldnumber=oldnumber,@oldid=userno from @t1 where id=@i
--以-号分隔所处理的字符串
Select @pos=charindex('-',@oldnumber)
Select @number=substring(@oldnumber,1,@pos-1),@count=substring(@oldnumber,@pos+1,len(@oldnumber))
Select @j=right(@number,len(@count))
while @j<=@count
begin
Select @newnumber=substring(@number,1,len(@number)-len(@j))+REPLICATE('0',len(@j))
insert into @t2 select @oldid,@newnumber+@j,@oldnumber
Select @j=@j+1
end
Select @i=@i+1
end
Select * from @t2
Drop table #T
insert #T(user_no,old_number) values(1 ,'13729554450-4499')
insert #T(user_no,old_number) values(2 ,'1398696450-99')
insert #T(user_no,old_number) values(3 ,'137467861150-1199')
go
Declare @t1 table(id int identity(1,1),userno varchar(30),oldnumber varchar(50))
insert into @t1
Select * from #T
Declare @t2 table(ID int,number varchar(30),OldNumber varchar(50))
Declare @i int,@Max int,@pos tinyint,@count int,@j bigint,@oldid int
Declare @oldnumber varchar(50),@number varchar(30),@newnumber varchar(30)
Select @i=Min(ID),@Max=Max(ID) from @t1
while @i<=@Max
begin
--代替游标迭代记录集,
Select @oldnumber=oldnumber,@oldid=userno from @t1 where id=@i
--以-号分隔所处理的字符串
Select @pos=charindex('-',@oldnumber)
Select @number=substring(@oldnumber,1,@pos-1),@count=substring(@oldnumber,@pos+1,len(@oldnumber))
Select @j=right(@number,len(@count))
while @j<=@count
begin
Select @newnumber=substring(@number,1,len(@number)-len(@j))+REPLICATE('0',len(@j))
insert into @t2 select @oldid,@newnumber+@j,@oldnumber
Select @j=@j+1
end
Select @i=@i+1
end
Select * from @t2
Drop table #T