首頁猿問請教下一個數(shù)據(jù)庫的問題。

請教下一個數(shù)據(jù)庫的問題。

SQL Server

開心每一天1111 2018-12-06 20:46:32

PersonId?????? TestDate????????????????????????? Valid 00000001?? ?2014-04-30 17:33:24.500?? ?000000001?? ?2014-04-30 17:34:14.420?? ?000000001?? ?2014-04-30 17:35:11.360?? ?000000006?? ?2013-12-17 08:42:21.047?? ?000000006?? ?2013-12-18 09:56:51.160?? ?000000020?? ?2013-12-12 10:46:33.307?? ?000000020?? ?2013-12-18 10:39:31.000?? ?000000132?? ?2013-12-02 14:35:34.603?? ?0 求解怎么更新表?設(shè)置相同PersonId?TestDate字段時間最大的Valid為1?其他的都為0?

查看完整描述

4 回答

白衣染霜花

TA貢獻1796條經(jīng)驗獲得超10個贊

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

反對回復 2019-01-07

婷婷同學_

TA貢獻1844條經(jīng)驗獲得超8個贊

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?

反對回復 2019-01-07