sql-server-2005 - 从子查询更新多列？

Question

如何避免以下 T-SQL 语句中的重复子查询:

update r set
  column1=(select top 1 max(column1) from region r2 where (overlapping test conditions)),
  column2=(select top 1 max(column2) from region r2 where (overlapping test conditions))
from region r

基本上，我有一个包含矩形区域的表格。对于每个重叠区域，我需要将一些列设置为该重叠区域的聚合值。

谢谢

编辑:我在这里添加了一个过度简化的重叠条件:

(r.left >= r2.left and r.left < r2.right) or (r.right <= r2.right and r.right > r.left)

关键是: r 和 r2 都将在子查询中被引用。似乎这是公用表表达式的完美情况，但我无法弄清楚如何为每条记录使用它。

Answer 1

UPDATE Region
SET
  Region.Column1 = r.Column1
  Region.Column2 = r.Column2
FROM
  Region
INNER JOIN 
 (
   SELECT
        Column1,
        Column2
   FROM Region
   WHERE (your condition here)
  ) r ON r.ID = Region.ID

我刚刚注意到 MAX() aggs。抱歉第一次没看到。
您可能必须有两个内部连接才能使其工作...

所以拿我所拥有的，为第 1 列做这件事

INNER JOIN (SELECT MAX(Column1) FROM ... WHERE ... ) r ON r.ID = Region.ID 

然后第二个 INNER JOIN

INNER JOIN (SELECT MAX(Column2) FROM ... WHERE ...)r2 ON r2.ID = Region.ID   

你的更新变成

UPDATE Region
SET Region.Column1 = r1.Column1,
    Region.Column2 = r2.Column2
FROM Region
INNER JOIN
(SELECT MAX(Column1) as Column1 FROM ... WHERE ... ) r ON r.ID = Region.ID
INNER JOIN
(SELECT MAX(Column2) As Column2 FROM ... WHERE ... ) r2 ON r2.ID = Region.ID