怎样Update
测试数据
create table tmpA (id int,TestID int,TestTypeID int,unitID int,Time1 datetime,Time2 datetime)
create table tmpB (id int,unitID int,TestTypeID int,Time1 datetime)
insert tmpA
select 980,6,9476210,1365917,'2013-08-10 13:45:25.027',NULL
union
select 981,6,9513833,1365917,'2013-08-15 08:50:41.020',NULL
union
select 982,6,9546296,1365917,'2013-08-20 01:41:49.550',NULL
...
insert tmpB
select 9533115,1365917,6,'2013-08-18 01:04:44.187'
union
select 9557041,1365917,6,'2013-08-21 05:00:37.907'
...
其中, tmpA.TestID = tmpB.ID, TestTypeID,unitID相同,就是想把A 最后两条Time2的时间update成B的最后两条时间
有很多UnitID，两张表的数量不一样，只想把最后tmpB的行数替换A相应的行 怎样Update
[解决办法]
DROP TABLE tmpA
DROP TABLE tmpB
go
create table tmpA (id int,TestID int,TestTypeID int,unitID int,Time1 datetime,Time2 datetime)
create table tmpB (id int,unitID int,TestTypeID int,Time1 datetime)
insert tmpA
select 980,6,9476210,1365917,'2013-08-10 13:45:25.027',NULL
union
select 981,6,9513833,1365917,'2013-08-15 08:50:41.020',NULL
union
select 982,6,9546296,1365917,'2013-08-20 01:41:49.550',NULL
insert tmpB(id, TestTypeID, unitID, Time1)--你的B表给的数据还是不对,我自己改了下
select 9533115,9513833,1365917,'2013-08-18 01:04:44.187'
union
select 9557041,9546296,1365917,'2013-08-21 05:00:37.907'
--sql:
SELECT * FROM tmpA
SELECT * FROM tmpB
UPDATE a
SET a.Time2 = b.Time1
FROM
(
SELECT rowid=ROW_NUMBER() OVER(PARTITION BY unitid, testtypeid ORDER BY id DESC), *
FROM tmpA
) a
INNER JOIN
(
SELECT rowid=ROW_NUMBER() OVER(PARTITION BY unitid, testtypeid ORDER BY Time1 DESC), *
FROM tmpB
) b
ON a.unitid = b.unitid
and a.testtypeid = b.testtypeid
AND a.rowid = b.rowid
--结果
SELECT * FROM tmpA
/*
idTestIDTestTypeIDunitIDTime1Time2
9806947621013659172013-08-10 13:45:25.027NULL
9816951383313659172013-08-15 08:50:41.0202013-08-18 01:04:44.187
9826954629613659172013-08-20 01:41:49.5502013-08-21 05:00:37.907
*/
