mssql sql语优化实例不使用insert语句

/
/*

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
              )    

        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
              )    

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)

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<=0 then
                     case when a.v2+b.V1<=a.vol_exchange then a.v2+b.V1 else a.vol_exchange end
         else  case when -(a.v1+b.V2)<= -b.vol_exchange then -(a.v1+b.V2) else -b.vol_exchange  end
         end as vol_exchange
 from
    (select top 100 percent * ,isnull((select sum(vol_exchange) from tb_a where mysort0),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 mysort0),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
 where  a.V2+b.V1>=0 and a.V1+b.V2<=0
) X
where vol_exchange>0

/*得出结果
owner_a  owner_b   skuid       in_lot    out_lot   vol_exchange 
---------------------------------------------------------------------------
78    06    1000164481    091201     071203     70.00
78    02    1000164479    091201     081203     28.00
78    03    1000164479    091201     081203     2.00
78    03    1000164479    091201     081202     20.00
78    03    1000164479    091201     081201     4.00
78    01    1000164479    091201     081201     6.00

*/

GO

drop table tb_a,tb_b