测试数据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
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
a.TestID = b.TestTypeID
a.unitid = b.unitid
and a.unitid = b.unitid
and a.testtypeid = b.testtypeid
SET a.Time2 = b.Time1
FROM tmpA a
CROSS APPLY
(SELECT TOP(1) time1 FROM tmpB m where a.TestID = m.TestTypeID AND m.unitid = a.unitid ORDER BY m.time1 desc) bSELECT * FROM tmpA
/*
id TestID TestTypeID unitID Time1 Time2
980 6 9476210 1365917 2013-08-10 13:45:25.027 2013-08-21 05:00:37.907
981 6 9513833 1365917 2013-08-15 08:50:41.020 2013-08-21 05:00:37.907
982 6 9546296 1365917 2013-08-20 01:41:49.550 2013-08-21 05:00:37.907
*/
UPDATE a
SET a.Time2 = n.Time1
FROM tmpA a
CROSS APPLY
(SELECT TOP(1) time1 FROM tmpB b where a.testid = b.id and a.unitid = b.unitid and a.testtypeid = b.testtypeid ORDER BY a.time1 desc) n
UPDATE a
SET a.Time2 = n.Time1
FROM tmpA a
CROSS APPLY
(SELECT TOP(1) time1 FROM tmpB b where a.testid = b.id and a.unitid = b.unitid and a.testtypeid = b.testtypeid ORDER BY b.time1 desc) n
--都给你说了,你给的数据不正确。
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',NULLinsert tmpB
select 9533115,1365917,6,'2013-08-18 01:04:44.187'
union
select 9557041,1365917,6,'2013-08-21 05:00:37.907'--sql:
SELECT * FROM tmpA
SELECT * FROM tmpB
/*
id TestID TestTypeID unitID Time1 Time2
980 6 9476210 1365917 2013-08-10 13:45:25.027 NULL
981 6 9513833 1365917 2013-08-15 08:50:41.020 NULL
982 6 9546296 1365917 2013-08-20 01:41:49.550 NULL
*/
/*
id unitID TestTypeID Time1
9533115 1365917 6 2013-08-18 01:04:44.187
9557041 1365917 6 2013-08-21 05:00:37.907
*/
但是,time2全部只更新成最大的了,我想要最后一个更新最大,倒数第二个更新b表的倒数第二个,依次类推
你再帮我看看吧,记得经常回复下,否则我无法发言了,谢谢现在a表3行,b表两行
我就是想把a表的倒数两行的time2,更新成b表的time1
以后可能还有很多的unitid进来
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',NULLinsert 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 tmpBUPDATE 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
/*
id TestID TestTypeID unitID Time1 Time2
980 6 9476210 1365917 2013-08-10 13:45:25.027 NULL
981 6 9513833 1365917 2013-08-15 08:50:41.020 2013-08-18 01:04:44.187
982 6 9546296 1365917 2013-08-20 01:41:49.550 2013-08-21 05:00:37.907
*/