求 sql先进先出查询解决办法????
商品编号 批次号 库存数量 0001 090801 200 0001 090501 50 0002 090101 30 0002 090701 200 商品编号 订货数量 0001 60 0002 20 要求结果: 商品编号 批次号 库存数量 出货数量 0001 090801 200 10 0001 090501 50 50 0002 090101 20 20 |
-
SQL code
-
-- > 测试数据: [a] if object_id ( ' [a] ' ) is not null drop table [ a ] create table [ a ] (商品编号 varchar ( 4 ),批次号 varchar ( 6 ),库存数量 int ) insert into [ a ] select ' 0001 ' , ' 090801 ' , 200 union all select ' 0001 ' , ' 090501 ' , 50 union all select ' 0002 ' , ' 090101 ' , 30 union all select ' 0002 ' , ' 090701 ' , 200 -- > 测试数据: if object_id ( ' ' ) is not null drop table [ b ] create table [ b ] (商品编号 varchar ( 4 ),订货数量 int ) insert into [ b ] select ' 0001 ' , 60 union all select ' 0002 ' , 20 select * From ( select t1. * ,出库数量 = case when ( select t2.订货数量 - isnull ( sum (库存数量), 0 ) from a where 商品编号 = t1.商品编号 and 批次号 <= t1.批次号) >= 0 then t1.库存数量 else case when ( select t2.订货数量 - isnull ( sum (库存数量), 0 ) from a where 商品编号 = t1.商品编号 and 批次号 < t1.批次号) < 0 then 0 else ( select t2.订货数量 - isnull ( sum (库存数量), 0 ) from a where 商品编号 = t1.商品编号 and 批次号 < t1.批次号) end end from a t1,b t2 where t1.商品编号 = t2.商品编号)a where 出库数量 > 0 -- 结果: 商品编号 批次号 库存数量 出库数量 -- -- ------ ----------- ----------- 0001 090801 200 10 0001 090501 50 50 0002 090101 30 20
SQL code
if object_id ( ' [ta] ' ) is not null drop table [ ta ] go create table [ ta ] ( [ 商品编号 ] varchar ( 4 ), [ 批次号 ] varchar ( 6 ), [ 库存数量 ] int ) insert [ ta ] select ' 0001 ' , ' 090801 ' , 200 union all select ' 0001 ' , ' 090501 ' , 50 union all select ' 0002 ' , ' 090101 ' , 30 union all select ' 0002 ' , ' 090701 ' , 200 if object_id ( ' [tb] ' ) is not null drop table [ tb ] go create table [ tb ] ( [ 商品编号 ] varchar ( 4 ), [ 订货数量 ] int ) insert [ tb ] select ' 0001 ' , 60 union all select ' 0002 ' , 20 select a. * , 出货数量 = a.库存数量 - case when sum (c.库存数量) - b.订货数量 < 0 then 0 else sum (c.库存数量) - b.订货数量 end from ta a join tb b on a.商品编号 = b.商品编号 join ta c on a.商品编号 = c.商品编号 and c.批次号 <= a.批次号 group by a.商品编号,a.批次号,a.库存数量,b.订货数量 having a.库存数量 > sum (c.库存数量) - b.订货数量 -- 测试结果: /* 商品编号 批次号 库存数量 出货数量 ---- ------ ----------- ----------- 0001 090501 50 50 0001 090801 200 10 0002 090101 30 20 (所影响的行数为 3 行)
having a.库存数量>sum(c.库存数量)-b.订货数量 这句起什么作用啊? 排除没用的库存 0002
SQL code -- -try declare @tb3 table (商品编号 nvarchar ( 10 ),批次号 nvarchar ( 10 ),库存数量 int ,出库数量 int ) declare @tb1 table (商品编号 nvarchar ( 10 ),批次号 nvarchar ( 10 ),库存数量 int ) insert into @tb1 select ' 0001 ' , ' 090801 ' , 200 union all select ' 0001 ' , ' 090501 ' , 50 union all select ' 0002 ' , ' 090101 ' , 30 union all select ' 0002 ' , ' 090701 ' , 200 declare @tb2 table (商品编号 nvarchar ( 10 ),订货数量 int ) insert into @tb2 select ' 0001 ' , 60 union all select ' 0002 ' , 20 -- declare @var int declare c_sor cursor for select 商品编号, sum (订货数量)订货数量 from @tb2 group by 商品编号 declare @bh nvarchar ( 10 ), @dh int , @bh1 nvarchar ( 10 ), @pc nvarchar ( 10 ), @kc int open c_sor fetch next from c_sor into @bh , @dh while @@fetch_status = 0 begin declare sor cursor for select * from @tb1 where 商品编号 = @bh order by 批次号 open sor fetch next from sor into @bh1 , @pc , @kc insert into @tb3 select @bh , @pc , @kc , case when @kc > @dh then @dh else @kc end set @dh = @dh - @kc while @dh > 0 begin fetch next from sor into @bh1 , @pc , @kc insert into @tb3 select @bh , @pc , @kc , case when @kc >= @dh then @dh else @kc end set @dh = @dh - @kc end close sor deallocate sor fetch next from c_sor into @bh , @dh end close c_sor deallocate c_sor select * from @tb3 order by 商品编号,批次号 desc /* (4 行受影响) (2 行受影响) (1 行受影响) (1 行受影响) (1 行受影响) 商品编号 批次号 库存数量 出库数量 ---------- ---------- ----------- ----------- 0001 090801 200 10 0001 090501 50 50 0002 090101 30 20 (3 行受影响)