下面有两个表:
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

解决方案 »

  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 行受影响)
    */
      

  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 行受影响)*/
      

  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 行受影响)
    */
      

  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 行受影响)
    */
      

  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 行受影响)
    */
      


  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
      

aliyun

类似问题 »