求一个sql语句，附测试sql，谢谢

[hovoy]

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

[道素]

因为根据你的条件没法根据现在的表确定唯一的记录，如果goodscode=001 and price=10 就有两行，所以我下面的例子，先定义一个确定的循序号，然后再进行记录.如果你的表已经这样的字段就不需要下面生成seq的逻辑

 ;with t as (
    select g.*,row_number()over(partition by g.goodscode order by g.price desc) as seq 
    from #temp as g 
 )
select t.goodscode,t.goodsname,t.num,t.price--,o.outnum,u.t_num
      ,case when isnull(u.t_num,0)>o.outnum then 0 else  case when o.outnum-isnull(u.t_num,0)>=t.num then t.num else o.outnum-isnull(u.t_num,0) end  end as OutNum
from t 
inner join  #temp_out as o  on t.goodscode=o.goodscode
outer apply(select sum(num) as t_num from t as ut where ut.goodscode=t.goodscode and ut.seq<t.seq) as u

+-----------+-----------+-----+-------+--------+
| goodscode | goodsname | num | price | OutNum |
+-----------+-----------+-----+-------+--------+
| 001       | aaa       | 1   | 15    | 1      |
| 001       | aaa       | 2   | 10    | 1      |
| 001       | aaa       | 1   | 10    | 0      |
| 002       | bbb       | 5   | 50    | 4      |
+-----------+-----------+-----+-------+--------+

[道素]

改成更行逻辑;

 ;with t as (
    select g.*,row_number()over(partition by g.goodscode order by g.price desc) as seq 
    from #temp as g 
 )
update t set t.outnum=case when isnull(u.t_num,0)>o.outnum then 0 else  case when o.outnum-isnull(u.t_num,0)>=t.num then t.num else o.outnum-isnull(u.t_num,0) end  end 
from t 
inner join  #temp_out as o  on t.goodscode=o.goodscode
outer apply(select sum(num) as t_num from t as ut where ut.goodscode=t.goodscode and ut.seq<t.seq) as u

select * from #temp

[二月十六]

;WITH ctea AS(
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY goodscode ORDER BY price DESC ) AS rn
FROM    #temp
),cteb AS (
SELECT  ctea.goodscode ,
        ctea.goodsname ,
        ctea.Num ,
        ctea.price,
		CASE WHEN Num>#temp_out.OutNum THEN #temp_out.OutNum ELSE Num END AS outnum,
		Num-#temp_out.OutNum AS tempnum,
		ctea.rn
FROM    ctea
        JOIN #temp_out ON #temp_out.goodscode = ctea.goodscode
WHERE   rn = 1
UNION ALL
SELECT 
ctea.goodscode ,
        ctea.goodsname ,
        ctea.Num ,
        ctea.price,
		CASE WHEN tempnum>=0 THEN 0 
		WHEN ctea.Num+tempnum>=0 THEN ABS(tempnum) ELSE ctea.Num END AS outnum,
		ctea.Num+tempnum,
		ctea.rn
 FROM ctea JOIN cteb ON cteb.goodscode = ctea.goodscode AND ctea.rn = cteb.rn+1
)
SELECT goodscode ,
       goodsname ,
       Num ,
       outnum 
	   FROM cteb ORDER BY goodscode,price DESC

[二月十六]

另外给楼主的提问方式点赞

[hovoy]

引用 4 楼 sinat_28984567 的回复:

另外给楼主的提问方式点赞

这个是？？？不过我觉得我弄个测试sql给大家，也是方便大家给我看问题。。省下好多时间。

[hovoy]

光顾着聊天。哈哈，忘记谢谢二位了。。