更新问题

Question

现有一张A表，由以下字段组成
合同编号  存货标识  单价      修改日期
001         A        20      2009-01-01 
001         A        30      2009-03-03
001         B        40      2009-01-01
002         A        70      2009-01-04
002         B        30      2009-03-04
002         B        40      2009-06-23
还有一张B表，是下面这个样子的
合同编号  存货标识   数量    单价   金额  发货日期
001         A         20      ?       ?    2009-01-23
001         A         30      ?       ?    2009-04-01
001         B         30      ？      ？   2009-02-04
002         A         40      ？      ？   2009-02-23
002         B         30      ？      ？   2009-02-28
002         B         20      ？      ？   2009-07-23我现在要执行更新把单价和金额更新到B表中
结果如下：
合同编号  存货标识   数量    单价   金额  发货日期
001         A         20      20     400    2009-01-23
001         A         30      30     900    2009-04-01
001         B         30      30     900   2009-02-04
002         A         40      40     1600   2009-02-23
002         B         30      30     900    2009-02-28
002         B         20      20      400   2009-07-23
是根据A表的修改日期和B表的发货日期做比较来得到这样一个更新结果，类似于就近原则。
当然关联的还有合同编号和存货标识，不晓得我表达清楚没，希望大家帮我解答下，谢谢，我写了半天没写出来，当B表实际中的数据会很多，我这只是简单的说下。大家帮我构造下这个SQL语句该怎么写，谢谢

Answer 1

update b
set 单价=a.单价,
    金额=数量*a.单价
from a ,b 
where a.合同编号=b.合同编号 and a.存货标志=b.存货标志

Answer 2

declare @price int
Update B
set @price=(select top 1 单价 From A where 合同编号=B.合同编号 
           and  存货标识=B.存货标识
           and 修改日期<=B.发货日期 
           order by 修改日期 Desc),
     单价=@price,
     金额=數量*@price

Answer 3

------------------------------------- 
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (合同编号 VARCHAR(3),存货标识 VARCHAR(1),单价 INT,修改日期 DATETIME)
INSERT INTO @tb1
SELECT '001','A',20,'2009-01-01' UNION ALL
SELECT '001','A',30,'2009-03-03' UNION ALL
SELECT '001','B',40,'2009-01-01' UNION ALL
SELECT '002','A',70,'2009-01-04' UNION ALL
SELECT '002','B',30,'2009-03-04' UNION ALL
SELECT '002','B',40,'2009-06-23'
 
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (合同编号 VARCHAR(3),存货标识 VARCHAR(1),数量 INT,单价 INT,金额 INT,发货日期 DATETIME)
INSERT INTO @tb2
SELECT '001','A',20,0,0,'2009-01-23' UNION ALL
SELECT '001','A',30,0,0,'2009-04-01' UNION ALL
SELECT '001','B',30,0,0,'2009-02-04' UNION ALL
SELECT '002','A',40,0,0,'2009-02-23' UNION ALL
SELECT '002','B',30,0,0,'2009-02-28' UNION ALL
SELECT '002','B',20,0,0,'2009-07-23'--SQL查询如下:update A set
    单价 = ISNULL((select top 1 单价 from @tb1
            where 合同编号=A.合同编号 and 存货标识=A.存货标识
            order by abs(datediff(day,修改日期,发货日期))),0),
    金额 = ISNULL((select top 1 单价 from @tb1
            where 合同编号=A.合同编号 and 存货标识=A.存货标识
            order by abs(datediff(day,修改日期,发货日期))),0) * 数量
from @tb2 AS A
select * from @tb2;/*
合同编号 存货标识 数量          单价          金额          发货日期
---- ---- ----------- ----------- ----------- -----------------------
001  A    20          20          400         2009-01-23 00:00:00.000
001  A    30          30          900         2009-04-01 00:00:00.000
001  B    30          40          1200        2009-02-04 00:00:00.000
002  A    40          70          2800        2009-02-23 00:00:00.000
002  B    30          30          900         2009-02-28 00:00:00.000
002  B    20          40          800         2009-07-23 00:00:00.000(6 行受影响)*/

Answer 4

001  A    20          20          400         2009-01-23 00:00:00.000
001  A    30          30          900         2009-04-01 00:00:00.000
001  B    30          40          1200        2009-02-04 00:00:00.000
002  A    40          70          2800        2009-02-23 00:00:00.000
002  B    30          30          900         2009-02-28 00:00:00.000
002  B    20          40          800         2009-07-23 00:00:00.000
我上面有条数据的金额错了，这个事准确的不好意思哈
多谢8楼的了