表TABLE1(name,date,dj)
A,2007-1-1,20.56
A,2007-1-1,21
A,2007-2-1,21
A,2007-2-1,20
表TABLE2(name,dj)
A,NULL我想把 表TABLE2中name为A的字段dj更新为 表TABLE1中name为A date取最大的其中任何一个dj即可即想将TABLE2(name,dj)更新为:
A,21 或 A,20请问用一句SQL可否完成更新工作呢,谢谢!!
A,2007-1-1,20.56
A,2007-1-1,21
A,2007-2-1,21
A,2007-2-1,20
表TABLE2(name,dj)
A,NULL我想把 表TABLE2中name为A的字段dj更新为 表TABLE1中name为A date取最大的其中任何一个dj即可即想将TABLE2(name,dj)更新为:
A,21 或 A,20请问用一句SQL可否完成更新工作呢,谢谢!!
from table2 as a
insert @a select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20declare @b table(name varchar(10),dj numeric(10,2))
insert @b select 'A',nullupdate s set
dj=(select top 1 dj from @a where name=gg.name and [date]=gg.[date]) from @b s
left join (select name,max([date]) [date] from @a group by name) gg
on s.name=gg.nameselect * from @b
update a set dj = (select max(dj) from table1 where name = a.name)
from table2 as a----或者
update a set dj = b.name
from table2 as a
inner join (select name, max(dj) as dj from table1 group by name) as b
on a.name = b.name
name varchar(10),
date varchar(10),
dj decimal(10,2))insert t1 select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20
create table t2(
name varchar(10),
dj decimal(10,2))insert t2 select 'A',null
---------------------------update t2
set dj = t.dj
from t1,
(
select name, max(dj) dj
from t1 a
where not exists (select 1 from t1 where date > a.date)
group by name
) t
where t1.name = t.name---------------------------
select * from t1
drop table t1, t2/*
name dj
---------- ------------
A 21.00(所影响的行数为 1 行)
*/
set dj=b.dj
from table2 a,table1 b
where a.name=b.name
and not exists (
select 1 from table1
where name=b.name
and date>b.date
)
insert T select 'A','2007-07-02',23
union all
select 'B','2007-07-02',233
create table TT(id varchar(10),dt datetime,fs int)
insert TT select 'A','2007-07-02',23
union all
select 'A','2007-07-02',233
union all
select 'A','2007-07-02',21
union all
select 'A','2007-07-02',22update a set a.fs=(select max(fs) from TT t where t.id=a.id ) from T as a,TT as aa where a.id=aa.id
insert @a select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20declare @b table(name varchar(10),dj numeric(10,2))
insert @b select 'A',null--不是max(dj),是max(date) 中的一个djupdate a set dj = (select max(dj) from @a where [name] = a.name and date in(select max(date) from @a where [name] = a.name))
from @b as aselect * from @b
/* 结果
A 21.00
*/
from @b as a
子查询之子查询,赞楼上的~
create table #t1
(
name varchar(10),
date datetime,
dj float
)gocreate table #t2
(
name varchar(10),
dj float
)
goinsert into #t1
select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20insert into #t2
select 'A',nullupdate #t2
set dj=(
select top 1 dj from #t1 O
where name=#t2.name
and date=(select max(date) from #t1
where name=O.name)
order by newid()
)select * from #t2drop table #t1
drop table #t2
update b set b.dj=a.dj from (select top 1 * from table1 a
where not exists(select 1 from table1 a.name=name and a.date<date)
order by newid()) a ,table2 b
where a.name=b.name
insert table1 select 'A','2007-1-1',20.56
union all select 'A','2007-1-1',21
union all select 'A','2007-2-1',21
union all select 'A','2007-2-1',20create table table2(name varchar(10),dj numeric(10,2))
insert table2 select 'A',nullupdate b set b.dj=a.dj from (select top 1 * from table1 a
where not exists(select 1 from table1 where a.name=name and a.date<date)
order by newid()) a ,table2 b
where a.name=b.nameselect * from table2