怎样Update

[jiang5311]

测试数据

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相应的行

[jiang5311]

还有人在不？没有回复啊

[Shawn]

引用 1 楼 jiang5311 的回复:

还有人在不？没有回复啊

tmpA.TestID = tmpB.ID, TestTypeID,unitID相同，是什么意思？

[jiang5311]

两表的列对应关系，a的=b的

[jiang5311]

大家帮忙看看吧，谢谢

[Shawn]

引用 4 楼 jiang5311 的回复:

大家帮忙看看吧，谢谢

楼主给的测试数据有问题吧？ a.TestID = b.TestTypeID a.unitid = b.unitid

[jiang5311]

a.testid = b.id and a.unitid = b.unitid and a.testtypeid = b.testtypeid

[Shawn]

--是这样吗？

UPDATE a
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) b

SELECT * 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
*/

[Shawn]

引用 6 楼 jiang5311 的回复:

a.testid = b.id and a.unitid = b.unitid and a.testtypeid = b.testtypeid

--试下这个：

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

[Shawn]

--order by后的a.time1 desc，应该改成b.time1 desc

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

[jiang5311]

刚才试了第一个，不行

[jiang5311]

我再看看后面的，谢谢你了

[jiang5311]

你自己试试呢，还是显示更新0行

[Shawn]

引用 12 楼 jiang5311 的回复:

你自己试试呢，还是显示更新0行

引用 6 楼 jiang5311 的回复:

a.testid = b.id and a.unitid = b.unitid and a.testtypeid = b.testtypeid

--都给你说了，你给的数据不正确。
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
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
*/

[jiang5311]

不是，是我搞错了，a.testid = b.id 应该去掉。结果可以出来了 但是，time2全部只更新成最大的了，我想要最后一个更新最大，倒数第二个更新b表的倒数第二个，依次类推

[jiang5311]

坑爹的CSDN，什么时候不能连续回复了，晕倒，对不起了 你再帮我看看吧，记得经常回复下，否则我无法发言了，谢谢 现在a表3行，b表两行 我就是想把a表的倒数两行的time2，更新成b表的time1 以后可能还有很多的unitid进来

[Shawn]

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
/*
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
*/

[jiang5311]

谢谢，先顶后看，辛苦了

[jiang5311]

解决了，谢谢，结贴