有两个表,结构如下:create table tab_aa
( x_date datetime not null,
x_id varchar(20) not null)goinsert into tab_aa
select '2006-01-01','a001' union all
select '2006-01-01','a002' union all
select '2006-01-01','a003' union all
select '2006-02-01','a001' union all
select '2006-02-01','a002' union all
select '2006-02-01','a003' gocreate table tab_bb
( b_date datetime not null,
b_id varchar(20) not null)
goinsert into tab_bb
select '2006-01-01','a001' union all
select '2006-01-02','a001' union all
select '2006-01-03','a001' union all
select '2006-01-08','a001' union all
select '2006-01-01','a002' union all
select '2006-01-04','a002' union all
select '2006-01-06','a002' union all
select '2006-01-08','a002' union all
select '2006-02-01','a001' union all
select '2006-02-04','a001' union all
select '2006-02-06','a001' union all
select '2006-02-08','a001' union all
select '2006-02-02','a002' union all
select '2006-02-06','a002' union all
select '2006-02-08','a002' union all
select '2006-02-10','a002'
go
现在要求把tab_bb 中二月份最大日期更新到tab_aa中
tab_aa
x_date x_id
2006-02-08 a001
2006-02-10 a002
( x_date datetime not null,
x_id varchar(20) not null)goinsert into tab_aa
select '2006-01-01','a001' union all
select '2006-01-01','a002' union all
select '2006-01-01','a003' union all
select '2006-02-01','a001' union all
select '2006-02-01','a002' union all
select '2006-02-01','a003' gocreate table tab_bb
( b_date datetime not null,
b_id varchar(20) not null)
goinsert into tab_bb
select '2006-01-01','a001' union all
select '2006-01-02','a001' union all
select '2006-01-03','a001' union all
select '2006-01-08','a001' union all
select '2006-01-01','a002' union all
select '2006-01-04','a002' union all
select '2006-01-06','a002' union all
select '2006-01-08','a002' union all
select '2006-02-01','a001' union all
select '2006-02-04','a001' union all
select '2006-02-06','a001' union all
select '2006-02-08','a001' union all
select '2006-02-02','a002' union all
select '2006-02-06','a002' union all
select '2006-02-08','a002' union all
select '2006-02-10','a002'
go
现在要求把tab_bb 中二月份最大日期更新到tab_aa中
tab_aa
x_date x_id
2006-02-08 a001
2006-02-10 a002
(select max(b_date) as b_date,b_id from tab_bb group by b_id )b
where tab_aa.x_id=b.b_id
update t1
set x_date = t2.b_date
from tab_aa t1 ,
(select a.x_id,max(b_date) as b_date
from tab_aa a ,tab_bb b
where a.x_id = b.b_id and month(x_date)=2 and month(b_date) = 2
group by a.x_id) t2
where t1.x_id = t2.x_id and month(x_date) = 2
declare @tab_aa table
( x_date datetime not null,
x_id varchar(20) not null)insert into @tab_aa
select '2006-01-01','a001' union all
select '2006-01-01','a002' union all
select '2006-01-01','a003' union all
select '2006-02-01','a001' union all
select '2006-02-01','a002' union all
select '2006-02-01','a003'
declare @tab_bb table
( b_date datetime not null,
b_id varchar(20) not null)insert into @tab_bb
select '2006-01-01','a001' union all
select '2006-01-02','a001' union all
select '2006-01-03','a001' union all
select '2006-01-08','a001' union all
select '2006-01-01','a002' union all
select '2006-01-04','a002' union all
select '2006-01-06','a002' union all
select '2006-01-08','a002' union all
select '2006-02-01','a001' union all
select '2006-02-04','a001' union all
select '2006-02-06','a001' union all
select '2006-02-08','a001' union all
select '2006-02-02','a002' union all
select '2006-02-06','a002' union all
select '2006-02-08','a002' union all
select '2006-02-10','a002' --计算
--select * from @tab_aa--select max(b_date) b_date,b_id from @tab_bb group by b_idupdate @tab_aa
set x_date = b.b_date
from (select max(b_date) b_date,b_id from @tab_bb group by b_id) b
where x_id = b_id
--结果
select * from @tab_aa-- 2006-02-08 00:00:00.000 a001
-- 2006-02-10 00:00:00.000 a002
-- 2006-01-01 00:00:00.000 a003
-- 2006-02-08 00:00:00.000 a001
-- 2006-02-10 00:00:00.000 a002
-- 2006-02-01 00:00:00.000 a003
1. 语句会把1月份的数据同时改掉
2. 如果tab_bb表里有三月份的数据的话,那样写会把tab_aa表里的数据改为三月份的数据.
go
insert into tab_aa
select max(b_date)b_date ,b_id from tab_bb group by b_Id