27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
create table AAA(
Productname varchar(16),
number int
)
create table BBB(
Productname varchar(16),
area varchar(32),
number int
)
insert into AAA values('商品A',50),('商品B',30)
insert into BBB values('商品A','上海',40),('商品B','上海',40),('商品A','北京',40),('商品B','北京',40)
select * from AAA
select * from BBB
create table AAA(
Productname varchar(16),
number int
)
create table BBB(
Productname varchar(16),
area varchar(32),
number int
)
insert into AAA values('商品A',50)
insert into AAA values('商品B',30)
insert into BBB values('商品A','上海',40)
insert into BBB values('商品B','上海',40)
insert into BBB values('商品A','北京',40)
insert into BBB values('商品B','北京',40)
insert into BBB values('商品A','四川',40)
insert into BBB values('商品B','四川',40)
--select * from AAA
--select * from BBB
;with t1 as(
select *,id=row_number() over(partition by [Productname] order by [area]) from bbb
),
t2 as(
select *,num=(select sum(number) from t1 where productname=t.productname and id<=t.id) from t1 t
)
select a.productname as 商品名,b.area as 提货仓库,
数量=case when b.num<a.number then b.number else
case when a.number-isnull((select top 1 num from t2 where productname=b.productname and id<b.id order by id desc),0)>0
then isnull(a.number-(select top 1 num from t2 where productname=b.productname and id<b.id order by id desc),a.number) else 0 end
end
from aaa a
join t2 b on a.productname=b.productname
/**
商品名 提货仓库 数量
---------------- -------------------------------- -----------
商品A 北京 40
商品A 上海 10
商品A 四川 0
商品B 北京 30
商品B 上海 0
商品B 四川 0
(6 行受影响)
**/
;with t as(
select *,id=row_number() over(partition by [Productname] order by [area]) from bbb
)
,t2 as
(
select t.Productname,t.area,
case when aaa.number>t.number then t.number else aaa.number end as number
, aaa.number-t.number as n,1 as [Level]
from aaa,t where aaa.Productname=t.Productname and t.id=1
union all
select t.Productname,t.area,
case when t2.n>t.number then t2.n-t.number else t2.n end
, t2.n-t.number ,[Level]=[Level]+1
from aaa a,t,t2 where a.Productname=t.Productname and t.Productname=t2.Productname and t2.n>0 and t.id=[Level]+1
)
select Productname,area,number from t2
order by Productname,area
/*
Productname area number
---------------- -------------------------------- -----------
商品A 北京 40
商品A 上海 10
商品B 北京 30
*/
;with t as(
select *,id=row_number() over(partition by [Productname] order by [area]) from bbb
)
,t2 as
(
select t.Productname,t.area,
case when aaa.number>t.number then t.number else aaa.number end as number
from aaa,t where aaa.Productname=t.Productname and t.id=1
union all
select b.Productname,b.area,
case when a.number>b.number then a.number-b.number else '' end
from aaa a,t b where a.Productname=b.Productname and b.id>1
)
select * from t2 where number>0 order by Productname,area
/*
Productname area number
---------------- -------------------------------- -----------
商品A 北京 40
商品A 上海 10
商品B 北京 30
*/
--测试数据
create table AAA(
Productname varchar(16),
number int
)
create table BBB(
Productname varchar(16),
area varchar(32),
number int
)
insert into AAA values('商品A',50),('商品B',30)
insert into BBB values('商品A','上海',40),('商品B','上海',40),('商品A','北京',40),('商品B','北京',40)
select * from AAA
select * from bbb
;with t as(
select *,id=row_number() over(partition by [Productname] order by [area]) from bbb
)
,t2 as
(
select t.Productname,t.area,
case when aaa.number>t.number then t.number else aaa.number end as number
from aaa,t where aaa.Productname=t.Productname and t.id=1
union all
select b.Productname,b.area,
case when a.number>b.number then a.number-b.number else b.number end
from aaa a,t b where a.Productname=b.Productname and b.id>1
)
select * from t2 order by Productname,area
/*
Productname area number
---------------- -------------------------------- -----------
商品A 北京 40
商品A 上海 10
商品B 北京 30
商品B 上海 40
*/