请教一条SQL语句，琢磨了好几天了，没想出来

Question

下面有两个表：
A表：
ID     Name    value
1      张三     null
2      李四     null
3      王五     null
4      赵六     null
5      钱七     null
B表：
ID     Name       Date      value
1      李四    2010-01-04    60
2      王五    2009-04-23    50
3      王五    2010-04-06    82
4      钱七    2009-11-23    40
5      钱七    2010-01-20    80
6      钱七    2010-03-05    70
7      赵六    2010-10-16    50
（划出一个时间段2010-05-31，在这个时间之前在B表中最接近这个时间的就更新至A表中，A.Name=B.Name)
我想要更新后的A表为如下：
ID     Name    value
1      张三     null
2      李四     60
3      王五     82
4      赵六     null
5      钱七     70

Answer 1

--> 测试数据：#ta
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta([ID] int,[Name] varchar(4),[value] sql_variant)
insert #ta
select 1,'张三',null union all
select 2,'李四',null union all
select 3,'王五',null union all
select 4,'赵六',null union all
select 5,'钱七',null--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([ID] int,[Name] varchar(4),[Date] datetime,[value] int)
insert #tb
select 1,'李四','2010-01-04',60 union all
select 2,'王五','2009-04-23',50 union all
select 3,'王五','2010-04-06',82 union all
select 4,'钱七','2009-11-23',40 union all
select 5,'钱七','2010-01-20',80 union all
select 6,'钱七','2010-03-05',70 union all
select 7,'赵六','2010-10-16',50--------------------------------查询开始------------------------------update  a set a.[value]=b.[value] from #ta a ,#tb b where
not exists(select 1 from #tb where name=b.name and date>b.date and date<'2010-05-31')
and b.date<'2010-05-31'
and a.name=b.nameselect * from #ta
/*
ID          Name value
----------- ---- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           张三   NULL
2           李四   60
3           王五   82
4           赵六   NULL
5           钱七   70(5 行受影响)
*/

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-05-09 14:23:09
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[Name] varchar(4),[value] sql_variant)
insert [A]
select 1,'张三',null union all
select 2,'李四',null union all
select 3,'王五',null union all
select 4,'赵六',null union all
select 5,'钱七',null
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[Name] varchar(4),[Date] datetime,[value] int)
insert [B]
select 1,'李四','2010-01-04',60 union all
select 2,'王五','2009-04-23',50 union all
select 3,'王五','2010-04-06',82 union all
select 4,'钱七','2009-11-23',40 union all
select 5,'钱七','2010-01-20',80 union all
select 6,'钱七','2010-03-05',70 union all
select 7,'赵六','2010-10-16',50
--------------开始查询--------------------------
update
  a
set
  [value]=b.[value]
from
  a
left join
  (select * from b t where [Date]=(select max([Date]) from b where name=t.name))b
on
  a.name=b.nameselect * from a
----------------结果----------------------------
/* ID          Name value
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           张三   NULL
2           李四   60
3           王五   82
4           赵六   50
5           钱七   70(5 行受影响)*/

Answer 3

--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
--       QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0    C Language 
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server  2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.a') is not null drop table a
-- 创建数据表
create table a
(
ID int,
Name char(5),
value int
)
go
--插入测试数据
insert into a select 1,'张三',null
union all select 2,'李四',null
union all select 3,'王五',null
union all select 4,'赵六',null
union all select 5,'钱七',null
go
if object_id('test.dbo.b') is not null drop table b
-- 创建数据表
create table b
(
ID int,
Name char(5),
Date char(11),
value int
)
go
--插入测试数据
insert into b select 1,'李四','2010-01-04',60
union all select 2,'王五','2009-04-23',50
union all select 3,'王五','2010-04-06',82
union all select 4,'钱七','2009-11-23',40
union all select 5,'钱七','2010-01-20',80
union all select 6,'钱七','2010-03-05',70
union all select 7,'赵六','2010-10-16',50
go
--代码实现
select a.ID,a.Name,tt.value from a left join (
select * from b t where not exists
(select 1 from b where Name=t.Name and Date>t.Date) )tt
on a.Name=tt.Name and tt.Date<'2010-05-31'/*测试结果ID   Name   value
---------------------
1 张三  NULL
2 李四  60
3 王五  82
4 赵六  NULL
5 钱七  70
(5 行受影响)
*/

Answer 4

写掉了时间段的条件
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-05-09 14:23:09
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[Name] varchar(4),[value] sql_variant)
insert [A]
select 1,'张三',null union all
select 2,'李四',null union all
select 3,'王五',null union all
select 4,'赵六',null union all
select 5,'钱七',null
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[Name] varchar(4),[Date] datetime,[value] int)
insert [B]
select 1,'李四','2010-01-04',60 union all
select 2,'王五','2009-04-23',50 union all
select 3,'王五','2010-04-06',82 union all
select 4,'钱七','2009-11-23',40 union all
select 5,'钱七','2010-01-20',80 union all
select 6,'钱七','2010-03-05',70 union all
select 7,'赵六','2010-10-16',50
--------------开始查询--------------------------
update
  a
set
  [value]=b.[value]
from
  a
left join
  (select * from b t where [Date]=(select max([Date]) from b where name=t.name))b
on
  a.name=b.name
and
  b.date<'2010-05-31'select * from a
----------------结果----------------------------
/* ID          Name value
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           张三   NULL
2           李四   60
3           王五   82
4           赵六   NULL
5           钱七   70(5 行受影响)
*/

Answer 5

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-09 14:39:00
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([ID] [int],[Name] [nvarchar](10),[value] [int])
INSERT INTO [A]
SELECT '1','张三',NULL UNION ALL
SELECT '2','李四',NULL UNION ALL
SELECT '3','王五',NULL UNION ALL
SELECT '4','赵六',NULL UNION ALL
SELECT '5','钱七',NULL--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([ID] [int],[Name] [nvarchar](10),[Date] [datetime],[value] [int])
INSERT INTO [B]
SELECT '1','李四','2010-01-04','60' UNION ALL
SELECT '2','王五','2009-04-23','50' UNION ALL
SELECT '3','王五','2010-04-06','82' UNION ALL
SELECT '4','钱七','2009-11-23','40' UNION ALL
SELECT '5','钱七','2010-01-20','80' UNION ALL
SELECT '6','钱七','2010-03-05','70' UNION ALL
SELECT '7','赵六','2010-10-16','50'--SELECT * FROM [A]
--SELECT * FROM [B]-->SQL查询如下:
update a set 
a.value=(select top 1 value from b where a.Name=b.Name and b.Date<'2010-5-31' order by date desc)select * from a
/*
ID          Name       value
----------- ---------- -----------
1           张三         NULL
2           李四         60
3           王五         82
4           赵六         NULL
5           钱七         70(5 行受影响)
*/

Answer 6

/*下面有两个表：
A表：
ID Name value
1 张三 null
2 李四 null
3 王五 null
4 赵六 null
5 钱七 null
B表：
ID Name Date value
1 李四 2010-01-04 60
2 王五 2009-04-23 50
3 王五 2010-04-06 82
4 钱七 2009-11-23 40
5 钱七 2010-01-20 80
6 钱七 2010-03-05 70
7 赵六 2010-10-16 50
*/
if object_id('tb') is not null drop table tb
create table tb(id int,name varchar(10),value int)
insert into tb(id,name)
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'赵六' union all
select 5,'钱七' if object_id('tb2') is not null drop table tb2
create table tb2(id int,name varchar(10),date datetime,value int)
insert into tb2
select 1,'李四','2010-01-04',60 union all
select 2,'王五','2009-04-23',50 union all
select 3,'王五','2010-04-06',82 union all
select 4,'钱七','2009-11-23',40 union all
select 5,'钱七','2010-01-20',80 union all
select 6,'钱七','2010-03-05',70 union all
select 7,'赵六','2010-10-16',50update tb set tb.value=t.value
from tb a,
(select name,value,date from tb2 b 
where date in (select max(date) from tb2 
group by name
having max(date)<='2010-05-31')) t
where a.name=t.name select * from tb
id      name    value
1 张三 NULL
2 李四 60
3 王五 82
4 赵六 NULL
5 钱七 70