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

with t
as(
select * from t1 a where a.id=(select max(id) from t1 b where a.s=b.s)
)
update t2
set t2.d=t1.id from t
where t2.s=t1=.s

------解决方案--------------------
SQL code
if object_id('tempdb..#t1') is not null drop table #t1
if object_id('tempdb..#t2') is not null drop table #t2
create table #t1(id int,s int,d int)
insert into #t1
select 1,1,1 union all
select 2,1,2 union all
select 3,1,3 union all
select 4,2,1 union all
select 5,3,1 
create table #t2(s int,d int)
insert into #t2
select 1,null 
-------------
select * from #t1
select * from #t2

update b set
  d = a.d
from #t1 a,#t2  b
where a.s = b.s
and not exists (
  select 1 from #t1
  where s=b.s
  and id > a.id
  )

select * from #t2
  
--结果
s    d
1    3

------解决方案--------------------
SQL code
update b
set d=a.d 
from t1 as a 
inner join t2 as b on a.s=b.s
where 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.s
inner join (select max(ID) as ID,s from t1 group by s)as c on a.ID=c.ID and a.s=c.s