求一个先进先出根据出库日期的SQL 万分感谢

[raysion2006]

这个是在论坛上找到的SQL代码，基本能满足我的要求。
目前就是有一个疑问就是 表2中需要增加一个出库日期字段。
然后在结果中显示每批次的货物，如果有出库，带上对应的出库日期。
（多个出库日期都能体现出来最好，不然以最早的出库日期为准）

原代码如下。
在这边先谢谢大家了。

create table 表1(货号 varchar(20),批次 int ,数量 int)

create table 表2(货号 varchar(20) ,数量 int)
/*------------------------------*/
insert into 表1
select '001', 1, 100 union all     
select '001', 2, 200 union all
select '002', 2, 200 union all
select '002', 4, 200 union all
select '111', 4, 200 union all
select '001', 3, 300

/*------------------------------*/
insert into 表2
select '001', 400 union ALL
select '002', 300


/*------------------------------*/
--select * from 表1

--select * from 表2
/*------------------------------*/
select t1.货号,t1.批次,
 case when ((select isnull(sum(数量),0) from 表1 t3 where t3.货号=t1.货号 and t3.批次<t1.批次)-isnull(t2.new_数量,0))<0 
 then case when ((select isnull(sum(数量),0) from 表1 t4 where t4.货号=t1.货号 and t4.批次<=t1.批次)-isnull(t2.new_数量,0))<0 then 0 --第一步
    else ((select isnull(sum(数量),0) from 表1 t4 where t4.货号=t1.货号 and t4.批次<=t1.批次)-isnull(t2.new_数量,0))--第二步
    end 
 else t1.数量--第三步
 end as 批次剩余库存数
from 表1 t1
 left join (select 货号,sum(数量) as new_数量 from 表2 group by 货号) t2
 on t1.货号=t2.货号
/*------------------------------*/

[raysion2006]

有没有高手帮帮忙啊？

[Tiger_Zhao]

加了日期，调整了一些测试数据

create table #t1(货号 varchar(20),批次 int,数量 int)

create table #t2(货号 varchar(20),日期 datetime, 数量 int)
/*------------------------------*/
insert into #t1
select '001', 1, 100 union all     
select '001', 2, 200 union all
select '002', 2, 200 union all
select '002', 4, 200 union all
select '111', 4, 200 union all
select '001', 3, 300 union all
select '003', 5, 100 union all
select '003', 6, 100 union all
select '003', 7, 100 

/*------------------------------*/
insert into #t2
select '001', '2015-10-01', 200 union ALL
select '001', '2015-10-02', 200 union ALL
select '002', '2015-10-03', 300 union ALL
select '003', '2015-10-04', 100

;WITH t1 AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY 货号 ORDER BY 批次) rn
      FROM #t1
)
,t2 AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY 货号 ORDER BY 日期) rn
      FROM #t2
)
,r AS (
    -- 货号的第一条
    SELECT t1.货号,
           t1.批次,
           t2.日期,
           t1.数量 AS 原数量,
           CASE WHEN t1.数量 <= ISNULL(t2.数量,0) THEN
                t1.数量
           ELSE
                t2.数量
           END AS 出库数量,
           t1.数量 - ISNULL(t2.数量,0) AS 剩余数量,
           t1.rn AS t1_rn,
           t2.rn AS t2_rn
      FROM t1
 LEFT JOIN t2
        ON t1.货号 = t2.货号
       AND t2.rn = 1
     WHERE t1.rn = 1
    UNION ALL -- 分配有剩余，继续下一个出库
    SELECT r.货号,
           r.批次,
           t2.日期,
           r.剩余数量 AS 原数量,
           CASE WHEN r.剩余数量 <= ISNULL(t2.数量,0) THEN
                r.剩余数量
           ELSE
                t2.数量
           END AS 出库数量,
           r.剩余数量 - ISNULL(t2.数量,0) AS 剩余数量,
           r.t1_rn,
           t2.rn AS t2_rn
      FROM r
      JOIN t2
        ON r.货号 = t2.货号
       AND t2.rn = r.t2_rn + 1
     WHERE r.剩余数量 > 0
    UNION ALL -- 分配不足，继续下一个批次
    SELECT r.货号,
           t1.批次,
           r.日期,
           t1.数量 AS 原数量,
           CASE WHEN t1.数量 <= ABS(r.剩余数量) THEN
                0
           ELSE
                ABS(r.剩余数量)
           END AS 出库数量,
           r.剩余数量 + t1.数量 AS 剩余数量,
           t1.rn AS t1_rn,
           r.t2_rn
      FROM r
      JOIN t1
        ON r.货号 = t1.货号
       AND t1.rn = r.t1_rn + 1
     WHERE r.剩余数量 < 0
    UNION ALL -- 正好分配完，两边都继续下一个
    SELECT t1.货号,
           t1.批次,
           t2.日期,
           t1.数量 AS 原数量,
           CASE WHEN t1.数量 <= ISNULL(t2.数量,0) THEN
                t1.数量
           ELSE
                t2.数量
           END AS 出库数量,
           t1.数量 - ISNULL(t2.数量,0) AS 剩余数量,
           t1.rn AS t1_rn,
           t2.rn AS t2_rn
      FROM r
      JOIN t1
        ON r.货号 = t1.货号
       AND t1.rn = r.t1_rn + 1
      JOIN t2
        ON r.货号 = t2.货号
       AND t2.rn = r.t2_rn + 1
     WHERE r.剩余数量 = 0
    UNION ALL -- 还要考虑出库分完后多余的批次
    SELECT r.货号,
           t1.批次,
           NULL AS 日期,
           t1.数量 AS 原数量,
           0 AS 出库数量,
           t1.数量 AS 剩余数量,
           t1.rn AS t1_rn,
           r.t2_rn
      FROM r
      JOIN t1
        ON r.货号 = t1.货号
       AND t1.rn = r.t1_rn + 1
     WHERE r.剩余数量 >= 0
       AND NOT EXISTS (
                        SELECT *
                          FROM t2
                         WHERE r.货号 = t2.货号
                           AND t2.rn > r.t2_rn
                      )
)
    SELECT 货号,批次,日期,原数量,出库数量,剩余数量
      FROM r
  ORDER BY 货号,
           (CASE WHEN 日期 IS NULL THEN 1 ELSE 0 END),
           日期
  --OPTION (MAXRECURSION 1000)

货号 批次 日期            原数量    出库数量    剩余数量
---- ---- ---------- ----------- ----------- -----------
001     1 2015-10-01         100         100        -100
001     2 2015-10-01         200         100         100
001     2 2015-10-02         100         100        -100
001     3 2015-10-02         300         100         200
002     2 2015-10-03         200         200        -100
002     4 2015-10-03         200         100         100
003     5 2015-10-04         100         100           0
003     6 NULL               100           0         100
003     7 NULL               100           0         100
111     4 NULL               200        NULL         200

又：你要的这个结果即有出库明细、又有当前库存，很混乱。
实际业务出入库必须有明细的单据，动态计算不合规矩。

[卖水果的net]

建议把这个对应关系放在一张表里面，并记录下数量，这样就容易多了。