SQL 難題

[misswangjinfeng]

假设ID为标识列，TS为时间戳，表A(ID,X,Y,Z,TS),表B(ID,X,Y,Z,TS);请编写一SQL语句实现，将B表中A.ID等于B.ID且A.TS不等于B.TS的记录的X、Y、Z值修改为A表中相应的X、Y、Z值

急啊！ 拜託啦!

[hao1hao2hao3]

update b set b.x=a.x,b.y=a.y,b.z=a.z from a join b on a.id = b.id where a.ts != b.ts

[「已注销」]

UPDATE B
SET B.X=M.X,B.Y=M.Y,B.Z=M.Z
FROM (SELECT * FROM A ,B
WHERE A.ID=B.ID AND A.TS<>B.TS)M

[dawugui]

[Quote=引用楼主 misswangjinfeng 的回复:]
假设ID为标识列，TS为时间戳，表A(ID,X,Y,Z,TS),表B(ID,X,Y,Z,TS);请编写一SQL语句实现，将B表中A.ID等于B.ID且A.TS不等于B.TS的记录的X、Y、Z值修改为A表中相应的X、Y、Z值

急啊！ 拜託啦!
[/Quote]

update b
set x = a.x,
y = a.y,
z = a.z
from b , a
where a.id = b.id and a.ts <> b.ts

[「已注销」]

UPDATE B
SET B.X=M.X,B.Y=M.Y,B.Z=M.Z
FROM (SELECT A.* FROM A ,B
WHERE A.ID=B.ID AND A.TS<>B.TS)M

[billpu]

IF EXISTS (SELECT * FROM sysobjects WHERE NAME ='tb1')
    DROP TABLE [tb1]
IF EXISTS (SELECT * FROM sysobjects WHERE NAME ='tb2')
    DROP TABLE [tb2]
CREATE TABLE tb1(ID INT,
X VARCHAR(10),Y VARCHAR(10),Z VARCHAR(10) ,TS DATETIME)
CREATE TABLE tb2(ID INT,
X VARCHAR(10),Y VARCHAR(10),Z VARCHAR(10) ,TS DATETIME)

INSERT Tb1 
SELECT 1,'1','1','1','2010-1-1 1:00' UNION ALL
SELECT 2,'1','1','1','2010-1-2 1:00' UNION ALL
SELECT 3,'1','1','1','2010-1-3 1:00' UNION ALL
SELECT 4,'1','1','1','2010-1-4 1:00' 
GO
INSERT Tb2 
SELECT 1,'2','2','2','2010-1-1 1:00' UNION ALL
SELECT 2,'2','2','2','2010-1-2 1:00' UNION ALL
SELECT 3,'2','2','2','2010-1-3 1:00' UNION ALL
SELECT 4,'2','2','2','2010-1-4 2:00' 
GO

UPDATE tb2 SET x=tb1.x,y=tb1.y,z=tb1.z FROM tb1 JOIN tb2 ON tb1.id=tb2.id
WHERE (tb1.ts<>tb2.ts)

SELECT * FROM tb2

/*
ID          X          Y          Z          TS                                                     
----------- ---------- ---------- ---------- ------------------------------------------------------ 
1           2          2          2          2010-01-01 01:00:00.000
2           2          2          2          2010-01-02 01:00:00.000
3           2          2          2          2010-01-03 01:00:00.000
4           1          1          1          2010-01-04 02:00:00.000

*/

[misswangjinfeng]

1樓滴，非常正確！給分！哈哈