34,576
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb_a') is not null drop table tb_a
go
create table tb_a
( owner char(2) null,
skuid varchar(10) null,
lot varchar(50) null,
quality varchar(2) null,
vol decimal(18,2) null,
vol_exchange decimal(18,2) null,
mysort int
)
if object_id('tb_b') is not null drop table tb_b
go
create table tb_b
( owner char(2) null,
skuid varchar(10) null,
lot varchar(50) null,
quality varchar(2) null,
vol decimal(18,2) null,
vol_exchange decimal(18,2) null,
mysort int
)
set nocount on
INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '78 ', '1000164481 ', '071203 ', '0 ',80.00,70.00,1)
INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '78 ', '1000164481 ', '071202 ', '0 ',60.00,0.00,2)
INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '78 ', '1000164481 ', '081203 ', '0 ',30.00,30.00,3)
INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '78 ', '1000164481 ', '081202 ', '0 ',20.00,20.00,4)
INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '78 ', '1000164481 ', '081201 ', '0 ',10.00,10.00,5)
INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '01 ', '1000164479 ', '091201 ', '0 ',18.00,6.00,6)
INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '04 ', '1000164481 ', '091201 ', '0 ',20.00,0.00,5)
INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '03 ', '1000164479 ', '091201 ', '0 ',26.00,26.00,4)
INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '02 ', '1000164479 ', '091201 ', '0 ',28.00,28.00,3)
INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '08 ', '1000164481 ', '091201 ', '0 ',60.00,0.00,2)
INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort)
VALUES ( '06 ', '1000164481 ', '091201 ', '0 ',80.00,70.00,1)
set nocount off
GO
--这段我简单看了一下阿,因为我也写过,只是写法不同,但是思路完全相同。但是很难理解
select * from
(
select a.owner as owner_a,b.owner as owner_b,b.skuid,b.lot as in_lot,a.lot as out_lot,
case when a.v2<=b.V2 then --当前B提供总数多与A的需要数量时
case when a.v2-b.V1<=a.vol_exchange -- B的剩余 <= 当前A的需要 显示B可提供的值,否则显示A的数量
then a.v2-b.V1 else a.vol_exchange end
else b.V2 - a.v1 --当前B可提供的和不能满足A的数量时,显示可以提供的数量
end as vol_exchange,
a.mysort asort,b.mysort bsort
from
(select top 100 percent * ,
isnull((select sum(vol_exchange) from tb_a where mysort<A.mysort and vol_exchange>0),0) as V1, --求出当前行之前所有需要分配的数量总合
isnull((select sum(vol_exchange) from tb_a where mysort<=A.mysort and vol_exchange>0),0) as V2 --求出到当前行所有需要分配的数量总合
from tb_a A
where vol_exchange>0
order by mysort) as A,
(select top 100 percent owner,skuid,lot,quality,vol,vol_exchange as vol_exchange,mysort ,
isnull((select sum(vol_exchange) from tb_b where mysort<B.mysort and vol_exchange>0),0) as V1,--求出当前行之前所有可以分配的数量总合
isnull((select sum(vol_exchange) from tb_b where mysort<=B.mysort and vol_exchange>0),0) as V2 --求出到当前行所有可以分配的数量总合
from tb_b B
where vol_exchange>0
order by mysort) as B
--这两个条件和在一起就是,当前A的所有vol_exchange的和要小于等于当前B提供的vol_exchange的和,但一定要大于等于B当前行之前所有vol_exchange的和
where a.V2>=b.V1 --这里就是当前需要分配总数 要大于等于 可提供的数数量(并且是不包含当前那个tb_b的可提供的数量(在第二个条件上判断包括当前B的和),刚好分配到B的最后一个或是倒数第二个合适的数据)
and a.V1<=b.V2 --这里判断B是否可以为A提供数据进行分配,V1是A可以分配的那行前所有 vol_exchange的和。也就是当前提供一定要满足为A当前行之前所有行的和,才能为当前A提供数据,当然这里有等号,包含了一些重复数据(即可以提供的数据是0的数据,这个是在最外层的select里除去)
/*
执行:
A=70
70 >= 0 and 0<= 70 (总需求 70 ,之前需求 0 ,符合但提供数据值为 70 );;2: 70>=70 and 0<=98;3: 70>=98 and 0<=124
A=30
100>=0 and 70<=70 (总需求 100 ,之前需求 70 ,符合但提供数据值为 0 ); 100>=70 and 70<=98 (总需求 100 ,之前需求 70 ,符合提供数据值为 28 );100>=98 and 70<=124 100>=124 and 70<=98
A=20
120>=0 and 124<=70 ...120>=98 and 100<=124 (总需求 100 ,之前需求 70 ,符合提供数据值为 20 );
......
*/
) X
where vol_exchange>0
order by asort,bsort
/*
owner_a owner_b skuid in_lot out_lot vol_exchange asort bsort
------- ------- ---------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------- ----------- -----------
78 06 1000164481 091201 071203 70.00 1 1
78 02 1000164479 091201 081203 28.00 3 3
78 03 1000164479 091201 081203 2.00 3 4
78 03 1000164479 091201 081202 20.00 4 4
78 03 1000164479 091201 081201 4.00 5 4
78 01 1000164479 091201 081201 6.00 5 6
(所影响的行数为 6 行)
*/