销售量进行每月分摊，求高手解决！
--已通过视图得到一张 BB表
idA年A月份A总产量A总销售数 未完成销售A剩余库存 B年 B月份 B销售量
1 201108 08 30 NULL NULL NULL 201111 11 540
2 201109 09 400 NULL NULL NULL 201111 11 540
3 201109 09 600 NULL NULL NULL 201111 11 540
4 201110 10 500 NULL NULL NULL 201111 11 540
5 201108 08 30 NULL NULL NULL 201110 10 100
6 201109 09 400 NULL NULL NULL 201110 10 100
7 201109 09 600 NULL NULL NULL 201110 10 100
目的是得到如下临时表：(要得到三列数据: A总销售数 未完成销售 A剩余库存)
id B月份 A总产量累加 A总产量 A总销售数 未完成销售 A剩余库存 B销售量
1 11 30 30 30 510 0 540
2 11 430 400 400 110 0 540
3 11 1030 600 110 0 490 540
4 11 1530 500 0 0 500 540
5 10 30 30 30 70 0 100
6 10 430 400 70 0 330 100
7 10 1030 600 0 0 600 100
--------
A剩余库存=A总产量-A总销售数
--------
以下是本人的语句，还未得以实现全部结果，只实现了一种情况,求高手解决！如有错误，请指正！
--------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[EE]
AS
BEGIN
create table #tmp
(id int identity(1,1) not null,
B月份 decimal(19,0),
A总产量累加 decimal(19,0),
A总产量 decimal(19,0),
A总销售数 decimal(19,0),
未完成销售 decimal(19,0),
A剩余库存 decimal(19,0),
B销售量 decimal(19,0),
primary key(id))
--truncate table #tmp
declare
@A1 decimal(19,0), --B月份
@A2 decimal(19,0), --A总产量累加
@A3 decimal(19,0), --A总产量
@A4 decimal(19,0), --A总销售数
@A5 decimal(19,0), --未完成销售
@A6 decimal(19,0), --A剩余库存
@A7 decimal(19,0) --B销售量
SET NOCOUNT ON;
declare cs_aa cursor for
WITH maco AS (SELECT *, row_number() OVER (partition BY B月份 ORDER BY B月份) AS RID FROM BB)SELECT B月份,(SELECT sum(A总产量) FROM maco WHERE B月份 = t .B月份 AND RID <= t .RID) AS A总产量累加, A总产量,A总销售数, 未完成销售, A剩余库存, B销售量 FROM maco t ORDER BY id ASC
open cs_aa
fetch next from cs_aa into @A1,@A2,@A3,@A4,@A5,@A6,@A7
while (@@fetch_status=0)
if @A7>@A2
begin
insert into #tmp(B月份,A总产量累加,A总产量,A总销售数,未完成销售,A剩余库存,B销售量) values (@A1,@A2,@A3,@A3,@A7-@A2,0,@A7)
fetch next from cs_aa into @A1,@A2,@A3,@A4,@A5,@A6,@A7
end
else
begin
insert into #tmp(B月份,A总产量累加,A总产量,A总销售数,未完成销售,A剩余库存,B销售量) values (@A1,@A2,@A3,@A3,0,@A2-@A7,@A7)
fetch next from cs_aa into @A1,@A2,@A3,@A4,@A5,@A6,@A7
end
close cs_aa
deallocate cs_aa
SELECT * FROM #tmp
end
[解决办法]
- SQL code
use Tempdbgo--> --> if not object_id(N'Tempdb..#T') is null drop table #TGoCreate table #T([id] int,[A年] Datetime,[A月份] nvarchar(2),[A产量] int,[A销售数] int,[未完成销售] int,[A剩余库存] int,[B年] Datetime,[B月份] int,[B销售量] int)Insert #Tselect 1,'201108',N'08',30,null,null,null,'201111',11,540 union allselect 2,'201109',N'09',400,null,null,null,'201111',11,540 union allselect 3,'201109',N'09',600,null,null,null,'201111',11,540 union allselect 4,'201110',N'10',500,null,null,null,'201111',11,540 union allselect 5,'201108',N'08',30,null,null,null,'201110',10,100 union allselect 6,'201109',N'09',400,null,null,null,'201110',10,100 union allselect 7,'201109',N'09',600,null,null,null,'201110',10,100GoSELECT id ,B月份 ,A总产量累加 ,A总产量 =[A产量] ,A总销售数 =CASE WHEN A总产量累加<=B销售量 THEN [A产量] WHEN B销售量>A总产量累加-[A产量] THEN B销售量-(A总产量累加-[A产量])ELSE 0 end ,未完成销售=CASE WHEN B销售量>A总产量累加 THEN B销售量-A总产量累加 else 0 end ,A剩余库存 =CASE WHEN A总产量累加-[A产量]>B销售量 THEN [A产量] WHEN A总产量累加>B销售量 THEN A总产量累加-B销售量 ELSE 0 end ,B销售量FROM (Select a.id ,B月份 ,A总产量累加=(SELECT SUM(A产量) FROM #T WHERE [id]<=a.[id] AND B年=a.B年 AND B月份=a.B月份),[A产量],a.B销售量 from #T AS a)T/*id B月份 A总产量累加 A总产量 A总销售数 未完成销售 A剩余库存 B销售量1 11 30 30 30 510 0 5402 11 430 400 400 110 0 5403 11 1030 600 110 0 490 5404 11 1530 500 0 0 500 5405 10 30 30 30 70 0 1006 10 430 400 70 0 330 1007 10 1030 600 0 0 600 100*/