playwarcraf和htl258 请接分 专家级高效SQL语句（500分）（第五贴共五贴

[dongdongxiao]

接上贴
http://topic.csdn.net/u/20100605/22/2c042cfb-1c5a-4fa8-93d6-d549b829f878.html?seed=1552929077&r=66030557#r_66030557


playwarcraf和htl258 请接分

[快乐_石头]

路过
.

[水族杰纶]

路过
.

[playwarcraft]

水哥，求包养。。。

[htl258_Tony]

捧场就好，不要给我分了。

[永生天地]

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 行）

*/

[-狙击手-]

。。。

[dongdongxiao]

[Quote=引用 5 楼 xys_777 的回复:]

SQL code


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,
……
[/Quote]

xys_777请进下帖
http://topic.csdn.net/u/20100607/11/9517a9c1-aec1-4f7a-8566-cf60ba5854e7.html