根据两表更新得到新表

Question

A表： 编号        产品编码         出库数量                                                                                                                                                                                                                                                   
----------- -----------   -----------  
1            1                 2 
2            1                 1
3            1                 5
4            2                 3
5            2                 2
6            3                 3
B表： 编号         产品编码       数量 
----------- ----------- ----------- --------- 
1              1             5 
2              2             4
3              3             2 结果表： 
编号         产品编码      出库数量                                                                                                                                                                                                                                                        
----------- -----------    -----------  
1             1                2        
2             1                1        
3             1                2 
4             2                3
5             2                1
6             3                2

Answer 1

declare @a table(编号 int,产品编码 int,出库数量 int)  
insert into @a(编号,产品编码,出库数量) values (1,1,2) 
insert into @a(编号,产品编码,出库数量) values (2,1,1) 
insert into @a(编号,产品编码,出库数量) values (3,1,5) 
insert into @a(编号,产品编码,出库数量) values (4,2,3) 
insert into @a(编号,产品编码,出库数量) values (5,2,2) 
insert into @a(编号,产品编码,出库数量) values (6,3,3) declare @b table(编号 int,产品编码 int,数量 int) 
insert into @b(编号,产品编码,数量) values (1,1,5) 
insert into @b(编号,产品编码,数量) values (2,2,4) 
insert into @b(编号,产品编码,数量) values (3,3,2)
编号          产品编码        出库数量        
----------- ----------- ----------- 
1           1           2
2           1           1
3           1           5
4           2           3
5           2           2
6           3           3（所影响的行数为 6 行）编号          产品编码        数量          
----------- ----------- ----------- 
1           1           5
2           2           4
3           3           2（所影响的行数为 3 行）编号   产品编码   出库数量         编号      产品编码        数量     对应分配数量    
------------ ----------- ----------- ----------- ----------------
1           1           2           1           1           5              2            
2           1           1           1           1           5              1           
3           1           5           1           1           5              2
4           2           3           2           2           4              3
5           2           2           2           2           4              1 
6           3           3           3           3           2              2

Answer 2

根据B表的数量对应地把A表该产品记录取出来，实际上是一个先进先出的问题
例如B表第一条记录产品编码为1，出货数为5，A表产品编码为1的记录有三条，要出货5个该产品，则取A表编号为1、2、3的三条记录才够出货

Answer 3

不知道说清楚没有？总之A表是进货表，B表是出货表，我要得到C表，C表的记录是根据B表数量从A表一条一条对应得到的

Answer 4

--> liangCK小梁 于2008-09-21
--> 生成测试数据: #TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
create table #TA (编号 int,产品编码 int,出库数量 int)
insert into #TA
select 1,1,2 union all
select 2,1,1 union all
select 3,1,5 union all
select 4,2,3 union all
select 5,2,2 union all
select 6,3,3
--> liangCK小梁 于2008-09-21
--> 生成测试数据: #TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
create table #TB (编号 int,产品编码 int,数量 int)
insert into #TB
select 1,1,5 union all
select 2,2,4 union all
select 3,3,2--SQL查询如下:SELECT A.编号,
       A.产品编码,
       A.出库数量,
       B.数量,
       [对应分配数量]=CASE 
                         WHEN A.p数量<B.数量 
                            THEN A.出库数量 
                         ELSE
                            B.数量-A.s数量
                      END
FROM
    (
       SELECT *,
              p数量=ISNULL((
                     SELECT SUM(出库数量) 
                     FROM #TA
                     WHERE 产品编码=t.产品编码
                        AND 编号<=t.编号
                    ),0),
              s数量=ISNULL((
                     SELECT SUM(出库数量) 
                     FROM #TA
                     WHERE 产品编码=t.产品编码
                        AND 编号<t.编号
                    ),0)
        FROM #TA AS t
    ) AS A
  JOIN
    #TB AS B
  ON A.产品编码=B.产品编码 /*
编号          产品编码        出库数量        数量          对应分配数量
----------- ----------- ----------- ----------- -----------
1           1           2           5           2
2           1           1           5           1
3           1           5           5           2
4           2           3           4           3
5           2           2           4           1
6           3           3           2           2(6 行受影响)*/

Answer 5

再提供一组数据：A表： 编号        产品编码        出库数量                                                                                                                                                                                                                                                  
----------- -----------  -----------  
1            1                2 
2            1                1 
3            1                1 
4            2                2 
5            2                1 
6            3                3 
B表： 编号        产品编码      数量 
----------- ----------- ----------- --------- 
1              1            5 
2              2            4 
3              3            2 结果表：
编号          产品编码        出库数量        数量          对应分配数量      
----------- ----------- ----------- ----------- ----------- 
1          1          6          5          2 
2          1          1          5          1 
3          1          5          5          1 
4          2          3          4          2 
5          2          2          4          1 
6          3          3          2          2 由此B表的数据将会被更新为：
B表： 编号        产品编码      数量             剩余未分配数量
----------- ----------- ----------- --------- 
1              1            5           1
2              2            4           1
3              3            2           0

Answer 6

--> liangCK小梁 于2008-09-21
--> 生成测试数据: #TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
create table #TA (编号 int,产品编码 int,出库数量 int)
insert into #TA
select 1,1,6 union all
select 2,1,1 union all
select 3,1,5 union all
select 4,2,3 union all
select 5,2,2 union all
select 6,3,3
--> liangCK小梁 于2008-09-21
--> 生成测试数据: #TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
create table #TB (编号 int,产品编码 int,数量 int)
insert into #TB
select 1,1,5 union all
select 2,2,4 union all
select 3,3,2--SQL查询如下:SELECT A.编号,
       A.产品编码,
       A.出库数量,
       B.数量,
       [对应分配数量]=CASE 
                          WHEN A.p数量<B.数量 
                             THEN A.出库数量 
                           ELSE
                              CASE 
                                  WHEN B.数量-A.s数量<=0 THEN 0
                                  ELSE B.数量-A.s数量
                              END
                           END
FROM
    (
       SELECT *,
              p数量=ISNULL((
                     SELECT SUM(出库数量) 
                     FROM #TA
                     WHERE 产品编码=t.产品编码
                        AND 编号<=t.编号
                    ),0),
              s数量=ISNULL((
                     SELECT SUM(出库数量) 
                     FROM #TA
                     WHERE 产品编码=t.产品编码
                        AND 编号<t.编号
                    ),0)
        FROM #TA AS t
    ) AS A
  JOIN
    #TB AS B
  ON A.产品编码=B.产品编码 GO/*
编号          产品编码        出库数量        数量          对应分配数量
----------- ----------- ----------- ----------- -----------
1           1           6           5           5
2           1           1           5           0
3           1           5           5           0
4           2           3           4           3
5           2           2           4           1
6           3           3           2           2(6 行受影响)*/