IF (SELECT COUNT(*)
FROM testtable AS A
WHERE ISNULL(NULLIF(Tel,''),'')<>'' AND ISNULL(NULLIF(Mobil,''),'')<>''
AND EXISTS(SELECT * FROM testtable
WHERE AddTime <> A.AddTime
AND (Tel = A.Tel OR Mobil = A.Mobil))) > 0
UPDATE testtable SET
MediaTel = (SELECT TOP 1 MediaTel FROM testtable
ORDER BY AddTime);
FROM testtable AS A
WHERE ISNULL(NULLIF(Tel,''),'')<>'' AND ISNULL(NULLIF(Mobil,''),'')<>''
AND EXISTS(SELECT * FROM testtable
WHERE AddTime <> A.AddTime
AND (Tel = A.Tel OR Mobil = A.Mobil))) > 0
UPDATE testtable SET
MediaTel = (SELECT TOP 1 MediaTel FROM testtable
ORDER BY AddTime);
select addtime,tel,mobil,mediatel=(select top 1 mediatel from testtable where (tel=a.tel or mobil=a.mobil) order by addtime)
from testtable a
SELECT 1
FROM TESTTABLE A,TESTTABLE B
WHERE A.AddTime > B.AddTime
AND (A.Tel = B.Tel
OR A.Mobil = B.Mobil )
AND A.MediaTel <> B.MediaTel
)
UPDATE A SET
MediaTel = B.MediaTel
FROM TESTTABLE A,TESTTABLE B
WHERE A.AddTime > B.AddTime
AND (A.Tel = B.Tel
OR A.Mobil = B.Mobil )
AND A.MediaTel <> B.MediaTel
UPDATE A SET
MediaTel = B.MediaTel
FROM TESTTABLE A,TESTTABLE B
WHERE A.AddTime > B.AddTime
AND (A.Tel = B.Tel
OR A.Mobil = B.Mobil )
AND A.MediaTel <> B.MediaTel
UPDATE A SET
MediaTel = B.MediaTel
FROM TESTTABLE A,TESTTABLE B
WHERE A.AddTime > B.AddTime
AND (A.Tel = B.Tel
OR A.Mobil = B.Mobil )
AND A.MediaTel <> B.MediaTel
select addtime,Tel,Mobil,MediaTel=(select top 1 MediaTel from testtable t where t.Tel=tel or Mobil=t.Mobil and t.Addtime>Addtime) from testtable/*
addtime Tel Mobil MediaTel
------------------------------------------------------ -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
2009-05-20 00:00:00.000 010-1234567 400-332-333
2009-05-22 00:00:00.000 132123456 400-332-333
2009-05-25 00:00:00.000 010-1234567 132123456 400-332-333(所影响的行数为 3 行)*/