记录拆分问题

use tempdb

if object_id('dbo.A') is not null

drop table dbo.A

if object_id('dbo.B') is not null

drop table dbo.B



create table dbo.A

(

  startmonth int ,

  endmonth  int,

  total int ,

  seller varchar(100)

)



create table dbo.B

(

  startmonth int ,

  total int ,

  seller varchar(100)

)



insert into dbo.A values(1,3,900,'张三')

insert into dbo.A values(2,5,2400,'李四')







declare @num int 

declare @st int 

declare @en int 

declare @to int 

declare @se varchar(100)

declare cur cursor for select * from dbo.A

open cur 

fetch next from cur into @st , @en ,@to , @se

while(@@fetch_status=0)

begin

set @num = @en-@st+1

while(@st<=@en)

begin

insert into dbo.B values (@st,@to/@num,@se)

set @st=@st+1

end

fetch next from cur into @st , @en ,@to,@se

end

close cur 

deallocate cur





select * from dbo.B

---测试数据---

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([开始月] int,[结束月] int,[总量] int,[销售员] varchar(4))

insert [tb]

select 1,3,900,'张三' union all

select 2,5,2400,'李四'

 

---查询---

select 

  开始月+b.number as 月份,

  总量/(结束月-开始月+1) as 销售量, 

  销售员

from

  tb a

join

  master..spt_values b

on

  开始月+b.number<=结束月 and b.type='P'



---结果---

月份          销售量         销售员

----------- ----------- ----

1           300         张三

2           300         张三

3           300         张三

2           600         李四

3           600         李四

4           600         李四

5           600         李四



(7 行受影响)