# [急]赠100分求解本论坛1周没解决的有关问题——寻高手！(3)

www.myexceptions.net  网友分享于：2013-03-13  浏览：10次

end as 金额,
case when isnull((select sum(sl) from # where id=a.id and id0> a.id0),0)> b.kc then a.sl
else (isnull((select sum(sl) from # where id=a.id and id0> =a.id0),0)-b.kc)
end as 数量
from # a,@kc b
where a.id=b.id
and isnull((select sum(sl) from # where id=a.id and id0> =a.id0),0)> b.kc
) as t
group by id

--结果
id 平均价
----------- --------------------------
1 5.525641025641
3 35.025316455696
4 37.427419354838
5 23.000000000000
7 32.814504881450
12 837.294743429286

（所影响的行数为 6 行）

------解决方案--------------------
--就这段代码
select d.*,k.kc,0 as 未售出 into #tmp from dj d join kc k on k.id = d.id order by d.id,ywrq desc
declare @kc int,@id int
update t
set @kc =
case
when t.id = @id then @kc-sl
else k.kc-sl
end
,@id = t.id,未售出 = case when @kc > 0 then sl when @kc+sl> 0 then @kc+sl else 0 end
from #tmp t
join kc k on k.id = t.id

select id,convert(numeric(10,2),convert(numeric(10,2),sum((sl-未售出)*dj))/sum(sl-未售出)) as 单价
from #tmp
group by id
drop table #tmp