我正在使用 JOIN 执行 SQL UPDATE,但该 JOIN 可以匹配多个值。假设我们有下表:
Table_1 Table_2
col_a | col_b col_a | col_b
--------------- ---------------
1 | A 1 | X
2 | B 1 | Y
3 | C 3 | Z
我执行以下查询:
UPDATE
t1
SET
t1.col_b = t2.col_b
FROM
Table_1 t1
JOIN
Table_2 t2 ON t1.col_a = t2.col_a;
结果如下:
Table_1 Table_2
col_a | col_b col_a | col_b
--------------- ---------------
1 | X 1 | X
2 | B 1 | Y
3 | Z 3 | Z
我需要做的是更新Table_1
与最后一个匹配的值;所以在这种情况下,我需要这个结果:
Table_1 Table_2
col_a | col_b col_a | col_b
--------------- ---------------
1 | Y 1 | X
2 | B 1 | Y
3 | Z 3 | Z
如果您有办法定义 Table_2 中记录的顺序(last 是什么意思?),您可以使用窗口函数来过滤 Table_2,以仅包含每组匹配记录的最后一条记录:
UPDATE
t1
SET
t1.col_b = t2.col_b
FROM
Table_1 t1
JOIN
(SELECT col_a, col_b,
ROW_NUMBER() OVER (PARTITION BY col_a
ORDER BY <order by field list goes here> DESC) AS RNo
FROM Table_2) t2 ON t1.col_a = t2.col_a AND t2.RNo=1;
在特殊情况下,order by 字段是 col_b 那么你可以简单地使用(这适用于所有版本的 SQL Server):
UPDATE
t1
SET
t1.col_b = t2.col_b
FROM
Table_1 t1
JOIN
(SELECT col_a, MAX(col_b) AS col_b
FROM Table_2
GROUP BY col_a) t2 ON t1.col_a = t2.col_a;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)