我有两个表A和B,
A中有两个字段,自增的主键id和字段:acontent
b中除了有一个自增的主键id,还有字段:aid, bcontent,和logdate(记录时间),其中aid对应A表中的id,也就是说A中的id和B中的aid是一对多的关系,我想做的是查询a表中acontent为空的记录,用B表中对应的根据id和aid的对应关系取出的最新记录的bcontent填入该记录的acontent中,如:
表A:
id acontent
1 a1
2 表 b
id aid bcontent logdate
1 2 aaa 2008-5-1
2 2 bbb 2008-5-2执行完这条sql语句后,A为
id acontent
1 a1
2 bbb这个sql语句该怎么写呢?
A中有两个字段,自增的主键id和字段:acontent
b中除了有一个自增的主键id,还有字段:aid, bcontent,和logdate(记录时间),其中aid对应A表中的id,也就是说A中的id和B中的aid是一对多的关系,我想做的是查询a表中acontent为空的记录,用B表中对应的根据id和aid的对应关系取出的最新记录的bcontent填入该记录的acontent中,如:
表A:
id acontent
1 a1
2 表 b
id aid bcontent logdate
1 2 aaa 2008-5-1
2 2 bbb 2008-5-2执行完这条sql语句后,A为
id acontent
1 a1
2 bbb这个sql语句该怎么写呢?
set acontent=(select top 1 bcontent from tb2 where aid=a.id order by logdate desc)
from tb1 a
where acontent is null
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (id int,acontent varchar(11))
insert into #a
select 1,'a1' union all
select 2,''
--> 测试数据: #b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b (id int,aid int,bcontent varchar(11),logdate datetime)
insert into #b
select 1,2,'aaa','2008-5-1' union all
select 2,2,'bbb','2008-5-2'update #a set acontent=b.bcontent from #a a join #b b on a.id=b.aid where a.acontent='' and b.logdate=(select max(logdate) from #b where aid=b.aid)select id,acontent from #a/*
id acontent
----------- -----------
1 a1
2 bbb
*/
create table #a (id int,acontent varchar(11))
insert into #a
select 1,'a1' union all
select 2,null
--> 测试数据: #b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b (id int,aid int,bcontent varchar(11),logdate datetime)
insert into #b
select 1,2,'aaa','2008-5-1' union all
select 2,2,'bbb','2008-5-2'
go
update a
set acontent=(select top 1 bcontent from #b where aid=a.id order by logdate desc)
from #a a
where acontent is nullselect * from #a
go
drop table #a,#b/*
id acontent
----------- -----------
1 a1
2 bbb(所影响的行数为 2 行)
*/
create table #a (id int,acontent varchar(11))
insert into #a
select 1,'a1' union all
select 2,null
--> 测试数据: #b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b (id int,aid int,bcontent varchar(11),logdate datetime)
insert into #b
select 1,2,'aaa','2008-5-1' union all
select 2,2,'bbb','2008-5-2'
go
update a
set acontent=(select top 1 bcontent from #b where aid=a.id order by logdate desc)
from #a a
where acontent is nullselect * from #a
go
drop table #a,#b/*
id acontent
----------- -----------
1 a1
2 bbb(所影响的行数为 2 行)*/
set acontent=(select top 1 bcontent from #b where aid=a.id order by logdate desc)
from #a a
where isnull(acontent,'')=''