求一句UPDATE解决方案

求一句UPDATE

SQL code

if object_id('tempdb..#t1') is not null drop table #t1if object_id('tempdb..#t2') is not null drop table #t2create table #t1(id int,s int,d int)insert into #t1select 1,1,1 union allselect 2,1,2 union allselect 3,1,3 union allselect 4,2,1 union allselect 5,3,1 create table #t2(s int,d int)insert into #t2select 1,null -------------select * from #t1select * from #t2-------------更新t2表的d值，条件是t2.s=t1.s ，但是s有重复，所以想取id最大的想要的t2结果是：s     d1     3

SQL code

with tas(select * from t1 a where a.id=(select max(id) from t1 b where a.s=b.s))update t2set t2.d=t1.id from twhere t2.s=t1=.s
[解决办法]
SQL code
if object_id('tempdb..#t1') is not null drop table #t1if object_id('tempdb..#t2') is not null drop table #t2create table #t1(id int,s int,d int)insert into #t1select 1,1,1 union allselect 2,1,2 union allselect 3,1,3 union allselect 4,2,1 union allselect 5,3,1 create table #t2(s int,d int)insert into #t2select 1,null -------------select * from #t1select * from #t2update b set  d = a.dfrom #t1 a,#t2  bwhere a.s = b.sand not exists (  select 1 from #t1  where s=b.s  and id > a.id  )select * from #t2  --结果s    d1    3
[解决办法]
SQL code
update bset d=a.d from t1 as a inner join t2 as b on a.s=b.swhere not exists(select 1 from t1 where s=a.s and id>a.id)
[解决办法]
SQL code
或update b set d=a.d  from t1 as a  inner join t2 as b on a.s=b.sinner join (select max(ID) as ID,s from t1 group by s)as c on a.ID=c.ID and a.s=c.s