怎样拆分成多条记录?谢谢
create table tmp
(
bh0 int ,
xm varchar(10) not null,
bh1 int
)
insert tmp
select 38001, 'AA ',38005 union all
select 39001, 'BB ',39003
要求拆分成如下结果:
bh0 xm bh1
------------------------
38001 AA 38005
38002 AA 38005
38003 AA 38005
38004 AA 38005
38005 AA 38005
39001 BB 39003
39002 BB 39003
39003 BB 39003
[解决办法]
declare @sql nvarchar (4000)
set @sql= ' '
select @sql=@sql+ REPLICATE ( ' union all select ' ' '+Convert(nvarchar(10),bh0)+ ' ' ' as bh0, ' ' '+xm+ ' ' ' as xm, ' ' '+Convert(nvarchar(10),bh1)+ ' ' ' as bh1 ' ,right(bh1,1))
from tmp
select @sql=right(@sql,len(@sql)-10)
print @sql
exec(@sql)
--result
38001AA38005
38001AA38005
38001AA38005
38001AA38005
38001AA38005
39001BB39003
39001BB39003
39001BB39003
[解决办法]
CREATE PROC prc
AS
BEGIN
DECLARE @tb TABLE(bh0 int,xm varchar(10),bh1 int)
DECLARE @i int
DECLARE @bh0 int, @xm varchar(10),@bh1 int
SET @i=0
DECLARE cur CURSOR FOR
SELECT bh0,xm,bh1 FROM tmp
OPEN cur
FETCH NEXT FROM cur
INTO @bh0, @xm ,@bh1
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE (@bh1-@bh0-@i)> =0
BEGIN
INSERT @tb select @bh0+@i, @xm ,@bh1
SET @i=@i+1
END
SET @i=0
FETCH NEXT FROM cur
INTO @bh0, @xm ,@bh1
END
CLOSE cur
DEALLOCATE cur
SELECT * FROM @tb
END
GO
EXEC prc
GO
/*
bh0 xm bh1
----------- ---------- -----------
38001 AA 38005
38002 AA 38005
38003 AA 38005
38004 AA 38005
38005 AA 38005
39001 BB 39003
39002 BB 39003
39003 BB 39003
（所影响的行数为 8 行）
*/
