27,579
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb..#temp') is not null
begin
truncate table #temp
drop table #temp
end
if object_id('tempdb..#temp_out') is not null
begin
truncate table #temp_out
drop table #temp_out
end
create table #temp
(
goodscode varchar(10) not null,
goodsname varchar(30) not null,
Num money null,
price money null,
OutNum money null --已免除或扣除
)
create table #temp_out
(
goodscode varchar(10) not null,
goodsname varchar(30) not null,
OutNum money null --已免除或扣除
)
insert into #temp(goodscode,goodsname,Num,Price,outNum)
values('001','aaa',2,10,0)
insert into #temp(goodscode,goodsname,Num,Price,outNum)
values('001','aaa',1,10,0)
insert into #temp(goodscode,goodsname,Num,Price,outNum)
values('001','aaa',1,15,0)
insert into #temp(goodscode,goodsname,Num,Price,outNum)
values('002','bbb',5,50,0)
---
insert into #temp_out(goodscode,goodsname,outNum)
values('001','aaa',2)
insert into #temp_out(goodscode,goodsname,outNum)
values('002','bbb',4)
select * from #temp_out
select * from #temp
--想让按price高的优先OutNum
--结果想如下显示:
/*
goodscode goodsname Num price outNum
001 aaa 2.0000 10.0000 1.0000
001 aaa 1.0000 10.0000 .0000 //也可以这里是1
001 aaa 1.0000 15.0000 1.0000
002 bbb 5.0000 50.0000 4.0000
*/
if object_id('tempdb..#temp') is not null
begin
truncate table #temp
drop table #temp
end
if object_id('tempdb..#temp_out') is not null
begin
truncate table #temp_out
drop table #temp_out
end
if object_id('tempdb..#temp') is not null
begin
truncate table #temp
drop table #temp
end
if object_id('tempdb..#temp_out') is not null
begin
truncate table #temp_out
drop table #temp_out
end
create table #temp
(
goodscode varchar(10) not null,
goodsname varchar(30) not null,
Num money null,
price money null,
OutNum money null --已免除或扣除
)
create table #temp_out
(
goodscode varchar(10) not null,
goodsname varchar(30) not null,
OutNum money null --已免除或扣除
)
insert into #temp(goodscode,goodsname,Num,Price,outNum)
values('001','aaa',2,10,0)
insert into #temp(goodscode,goodsname,Num,Price,outNum)
values('001','aaa',1,10,0)
insert into #temp(goodscode,goodsname,Num,Price,outNum)
values('001','aaa',1,15,0)
insert into #temp(goodscode,goodsname,Num,Price,outNum)
values('002','bbb',5,50,0)
---
insert into #temp_out(goodscode,goodsname,outNum)
values('001','aaa',2)
insert into #temp_out(goodscode,goodsname,outNum)
values('002','bbb',4)
declare @goodscode nvarchar(10),@goodsname varchar(30),@Num money ,@price money ,@OutNum money ,@rn INT
declare auth_cur cursor for
select goodscode,goodsname,Num,Price,outNum
from #temp ORDER BY price DESC
open auth_cur
fetch next from auth_cur into @goodscode,@goodsname,@Num, @price, @OutNum
while (@@fetch_status=0)
BEGIN
DECLARE @out INT
SELECT @out=OutNum FROM #temp_out WHERE goodscode=@goodscode
PRINT @out
IF(@out>@Num)
BEGIN
UPDATE #temp SET OutNum = Num WHERE goodscode=@goodscode AND Num=@Num AND price=@price
UPDATE #temp_out SET OutNum=@out-(SELECT OutNum FROM #temp WHERE goodscode=@goodscode AND Num=@Num AND price=@price) WHERE goodscode=@goodscode
END
ELSE
BEGIN
UPDATE #temp SET OutNum = @out WHERE goodscode=@goodscode AND Num=@Num AND price=@price
UPDATE #temp_out SET OutNum=0 WHERE goodscode=@goodscode
END
fetch next from auth_cur into @goodscode,@goodsname,@Num, @price, @OutNum
end
close auth_cur
deallocate auth_cur
SELECT * FROM #temp
if object_id('tempdb..#temp') is not null
begin
truncate table #temp
drop table #temp
end
if object_id('tempdb..#temp_out') is not null
begin
truncate table #temp_out
drop table #temp_out
end
select *,identity(int,1,1) as id into #temp_2 from #temp ORDER BY PRICE
select *,(select count(1) from #temp_2 where A.goodscode=goodscode and A.goodsname=goodsname and A.id<=id) as seq into #temp_3 from #temp_2 A
select A.goodscode,A.goodsname,A.num,A.price,B.outnum,
(select sum(num) from #temp_3 where seq<=A.seq and A.goodscode=goodscode and A.goodsname=goodsname) as sub_total
into #temp_4
from #temp_3 A
join #temp_out B ON A.goodscode=B.goodscode and A.goodsname=B.goodsname
select *,
case when outnum>=sub_total then num
when outnum<sub_total and outnum>sub_total-num then num-(sub_total-outnum)
when outnum<=sub_total-num then 0
end as outnum_final
from #temp_4