declare @t table (
[Index] varchar(10),
No int,
Total int,
Spare int
)
insert @t
select
'L08', 2, 50, 10 select top 1000 IDENTITY(int,1,1) as id into #t from sysobjects a,sysobjects bselect
[Index]+right('0000'+cast(t.id as varchar),4) as [Index],
a.Total
from @t a,#t t
where t.id<=a.No
union all
select
[Index]+right('0000'+cast(No+1 as varchar),4) as [Index],
Spare as Total
from @tdrop table #t--结果
Index Total
------------------ -----------
L080001 50
L080002 50
L080003 10(所影响的行数为 3 行)
[Index] varchar(10),
No int,
Total int,
Spare int
)
insert @t
select
'L08', 2, 50, 10 select top 1000 IDENTITY(int,1,1) as id into #t from sysobjects a,sysobjects bselect
[Index]+right('0000'+cast(t.id as varchar),4) as [Index],
a.Total
from @t a,#t t
where t.id<=a.No
union all
select
[Index]+right('0000'+cast(No+1 as varchar),4) as [Index],
Spare as Total
from @tdrop table #t--结果
Index Total
------------------ -----------
L080001 50
L080002 50
L080003 10(所影响的行数为 3 行)
insert into tb values('L08', 2, 50, 10 )
goselect [index] = m.[index] + right('0000' + cast(0 + n.id as varchar),4) , total from tb m,
(select 1 as id union select 2 union select 3 union select 4 union select 5) n
where 0 + n.id <= m.[no]
union all
select [index] = [index] + right('0000' + cast(1 + [no] as varchar),4) , spare total from tbdrop table tb/*
index total
------------------ -----------
L080001 50
L080002 50
L080003 10(3 行受影响)
*/
L08 2 50 10 一个LabelID 有两包,每包50个产品,还有备品数10个装成一包.
现在需要执行存储过程或sql后得到如下的结果LabelID UnitQty
L080001 50 -- 第一包
L080002 50 -- 第二包
L080003 10 -- 备品数
就是在原来LabelID的基础上加上后边的序号 是4为 依次如:0001,0002,0003,.........大家帮帮忙哈