作者jengting (~~)
看板Database
标题Re: [SQL ] 求救SQL语法
时间Mon Mar 4 11:59:50 2013
-- 建立 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