如果您想要向TableA添加新列并从表B中更新它的值,这是最终的UPDATE语句:
UPDATE TableA
SET Colour = B.Colour
FROM TableA A
INNER JOIN TableB B ON B.Month = A.Month AND B.RangeStart < A.Average AND (B.RangeEnd IS NULL OR B.RangeEnd > A.Average)
您应该使用左外连接,并在可能没有表B中的数据来计算某个月份和A表中的平均值时提供默认值。
UPDATE TableA
SET Colour = ISNULL(B.Colour, N'SomeColour')
FROM TableA A
LEFT OUTER JOIN TableB B ON B.Month = A.Month AND B.RangeStart < A.Average AND (B.RangeEnd IS NULL OR B.RangeEnd > A.Average)
此外,您应该检查表B中的数据是否依赖于年份,就像表A一样。
在这种情况下,您应该将此字段添加到Join的On子句中:
UPDATE TableA
SET Colour = B.Colour
FROM TableA A
INNER JOIN TableB B ON B.Year = A.Year AND B.Month = A.Month AND B.RangeStart < A.Average AND (B.RangeEnd IS NULL OR B.RangeEnd > A.Average)
在运行更新之前,您可以先运行此查询来检查是否获得了所需的值:
SELECT Department, A.Month, Average, B.Colour
FROM @tableA A
INNER JOIN @tableB B ON B.Month = A.Month AND B.RangeStart < A.Average AND (B.RangeEnd IS NULL OR B.RangeEnd > A.Average)
希望这能有所帮助 :)