Re: [SQL ] 求救SQL語法
-- 建立 Sample Data
DECLARE @CUC1 TABLE (POP char(5),AGT char(4),CLI char(4))
DECLARE @AGTC TABLE (AGT char(4),CLI char(4),LastTime datetime)
INSERT INTO @CUC1 VALUES('A1234','1314','')
INSERT INTO @CUC1 VALUES('B1235','1314','')
INSERT INTO @CUC1 VALUES('C1213','1423','')
INSERT INTO @AGTC VALUES('1314','A122','2013-01-01')
INSERT INTO @AGTC VALUES('1423','A222','2013-02-01')
INSERT INTO @AGTC VALUES('1423','123A','2013-02-02')
-- 更新 @CUC1 的 CLI 欄位資料
UPDATE C
SET C.CLI = A.CLI
FROM @CUC1 AS C
JOIN
(
SELECT * ,
ROW_NUMBER() OVER (PARTITION BY AGT ORDER BY LastTime DESC ) AS ROWNO
FROM @AGTC
-- WHERE Condition 建議加個 WHERE 條件，避免對太多資料排序
) AS A ON C.AGT = A.AGT AND A.ROWNO = 1
-- 顯示更新後結果
SELECT * FROM @CUC1
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 118.163.158.7
討論串 (同標題文章)
完整討論串 (本文為第 2 之 2 篇)：