请教下一个数据库的问题。

Question

PersonId       TestDate                          Valid

00000001    2014-04-30 17:33:24.500    0
00000001    2014-04-30 17:34:14.420    0
00000001    2014-04-30 17:35:11.360    0
00000006    2013-12-17 08:42:21.047    0
00000006    2013-12-18 09:56:51.160    0
00000020    2013-12-12 10:46:33.307    0
00000020    2013-12-18 10:39:31.000    0
00000132    2013-12-02 14:35:34.603    0

求解 怎么更新表 设置相同PersonId TestDate字段时间最大的Valid为1 其他的都为0 

Answer 1

update a set Valid=1 from a inner join (select distinct PersonId,MAX(TestDate) TestDate from a group by PersonId) b on a.PersonId=b.PersonId and a.TestDate=b.TestDate

Answer 2

update tbl set valid=1 from (select *,row_number() over(partition by PersonId order by TestDate desc) as rId from tbl) b where tbl.PersonId=b.PersonId and tbl.TestDate=b.TestDate and b.rId=1