Re: [SQL ] 求救SQL語法

[jengting (~~)]

-- 建立 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