with TB as( select max(chid) chid,ch,chdate,ROW_NUMBER()over(order by chdate desc) rowid from ch_info group by ch,chdate)update #tmp1 set chid=b.chid,chdate=b.chdate from #tmp1 as a inner join (select chid,CH,chdate from TB where rowid=1) as b on a.ch=b.ch
UPDATE #tmp1 SET #tmp1.chid = b.chid , #tmp1.chdate = b.chdate FROM #tmp1 INNER JOIN ( SELECT MAX(chid) chid , ch , chdate FROM ch_info a WHERE EXISTS ( SELECT 1 FROM ( SELECT ch , MAX(chdate) chdate FROM ch_info GROUP BY ch ) b WHERE a.ch = b.ch AND a.chdate = b.chdate ) GROUP BY ch , chdate ) b ON #tmp1.ch = b.ch
纠正下: with TB as( select max(chid) chid,ch,chdate,ROW_NUMBER()over(partition by ch order by chdate desc) rowid from ch_info group by ch,chdate)
update #tmp1 set chid=b.chid,chdate=b.chdate from #tmp1 as a inner join (select chid,CH,chdate from TB where rowid=1) as b on a.ch=b.ch
create table ch_info(chid varchar(10),ch varchar(20),chdate datetime) insert into ch_info select '2544459','A00000248A959E','2011-01-01' insert into ch_info select '2564248','A00000248A959E','2011-01-01' insert into ch_info select '2569995','A00000248A959E','2010-12-31' create table #tmp1(chid varchar(10),ch varchar(20),chdate datetime) insert into #tmp1 select null,'A00000248A959E',null go update a set chid=b.chid,chdate=b.chdate from #tmp1 a inner join ch_info b on a.ch=b.ch where not exists(select 1 from ch_info where ch=b.ch and (datediff(d,b.chdate,getdate())>datediff(d,chdate,getdate()) or (datediff(d,b.chdate,getdate())=datediff(d,chdate,getdate()) and chid>b.chid))) select * from #tmp1 /* chid ch chdate ---------- -------------------- ----------------------- 2564248 A00000248A959E 2011-01-01 00:00:00.000(1 行受影响)*/ go drop table ch_info,#tmp1
create table ch_info(chid varchar(10),ch varchar(20),chdate datetime) insert into ch_info select '2544459','A00000248A959E','2011-01-01' insert into ch_info select '2564248','A00000248A959E','2011-01-01' insert into ch_info select '2569995','A00000248A959E','2010-12-31' create table #tmp1(chid varchar(10),ch varchar(20),chdate datetime) insert into #tmp1 select null,'A00000248A959E',null go update a set chid=b.chid,chdate=b.chdate from #tmp1 a inner join ch_info b on a.ch=b.ch where not exists(select 1 from ch_info where ch=b.ch and (datediff(d,b.chdate,getdate())>datediff(d,chdate,getdate()) or (datediff(d,b.chdate,getdate())=datediff(d,chdate,getdate()) and chid>b.chid))) select * from #tmp1 /* chid ch chdate ---------- -------------------- ----------------------- 2564248 A00000248A959E 2011-01-01 00:00:00.000(1 行受影响)*/ go drop table ch_info,#tmp1
这样更快一点点: update a set chid=b.chid,chdate=b.chdate from #tmp1 a inner join( select *,row_number()over(partition by ch order by chdate desc,chid desc)rn from ch_info )b on a.ch=b.ch and b.rn=1
select max(chid) chid,ch,chdate,ROW_NUMBER()over(order by chdate desc) rowid
from ch_info
group by ch,chdate)update #tmp1 set chid=b.chid,chdate=b.chdate
from #tmp1 as a inner join (select chid,CH,chdate from TB where rowid=1) as b on a.ch=b.ch
SET #tmp1.chid = b.chid ,
#tmp1.chdate = b.chdate
FROM #tmp1
INNER JOIN ( SELECT MAX(chid) chid ,
ch ,
chdate
FROM ch_info a
WHERE EXISTS ( SELECT 1
FROM ( SELECT ch ,
MAX(chdate) chdate
FROM ch_info
GROUP BY ch
) b
WHERE a.ch = b.ch
AND a.chdate = b.chdate )
GROUP BY ch ,
chdate
) b ON #tmp1.ch = b.ch
with TB as(
select max(chid) chid,ch,chdate,ROW_NUMBER()over(partition by ch order by chdate desc) rowid
from ch_info
group by ch,chdate)
update #tmp1 set chid=b.chid,chdate=b.chdate
from #tmp1 as a inner join (select chid,CH,chdate from TB where rowid=1) as b on a.ch=b.ch
insert into ch_info select '2544459','A00000248A959E','2011-01-01'
insert into ch_info select '2564248','A00000248A959E','2011-01-01'
insert into ch_info select '2569995','A00000248A959E','2010-12-31'
create table #tmp1(chid varchar(10),ch varchar(20),chdate datetime)
insert into #tmp1 select null,'A00000248A959E',null
go
update a set chid=b.chid,chdate=b.chdate from #tmp1 a inner join ch_info b on a.ch=b.ch
where not exists(select 1 from ch_info where ch=b.ch and (datediff(d,b.chdate,getdate())>datediff(d,chdate,getdate())
or (datediff(d,b.chdate,getdate())=datediff(d,chdate,getdate()) and chid>b.chid)))
select * from #tmp1
/*
chid ch chdate
---------- -------------------- -----------------------
2564248 A00000248A959E 2011-01-01 00:00:00.000(1 行受影响)*/
go
drop table ch_info,#tmp1
insert into ch_info select '2544459','A00000248A959E','2011-01-01'
insert into ch_info select '2564248','A00000248A959E','2011-01-01'
insert into ch_info select '2569995','A00000248A959E','2010-12-31'
create table #tmp1(chid varchar(10),ch varchar(20),chdate datetime)
insert into #tmp1 select null,'A00000248A959E',null
go
update a set chid=b.chid,chdate=b.chdate from #tmp1 a inner join ch_info b on a.ch=b.ch
where not exists(select 1 from ch_info where ch=b.ch and (datediff(d,b.chdate,getdate())>datediff(d,chdate,getdate())
or (datediff(d,b.chdate,getdate())=datediff(d,chdate,getdate()) and chid>b.chid)))
select * from #tmp1
/*
chid ch chdate
---------- -------------------- -----------------------
2564248 A00000248A959E 2011-01-01 00:00:00.000(1 行受影响)*/
go
drop table ch_info,#tmp1
ch_info在100W以上
#tmp1在2W以上
所以要考虑效率问题这个让我比较发愁
update a set chid=b.chid,chdate=b.chdate from #tmp1 a inner join(
select *,row_number()over(partition by ch order by chdate desc,chid desc)rn from ch_info
)b on a.ch=b.ch and b.rn=1