在表B范围内，基于表A的值进行SQL连接

Question

我有两个表格，可以在附带的图像中看到。

表格A包含部门、月份和平均值。

表格B包含月份、年份、范围开始、范围结束和颜色。

如果您查看表格B的截屏，您将看到每个月都有一个绿色、黄色、橙色和红色的值。您还有一个范围。

我需要......

我需要在表格A上添加一个名为“颜色”的新列。在这一列中，我需要绿色、黄色、橙色或红色。决定分配给该月的颜色的因素将是“平均”列。

例如：

五月的DepartmentA平均值等于0.96 在参考表格B之后，我可以看到第8行0.75+是它所适合的范围。因此，我想要将红色放置在Mays平均值旁边的表格A中。

我将每个月的最高范围的范围结束留空，因为它基本上是75+，任何大于0.75的值都可以放在这里。

有人能给我指点一个不太耗时的方向吗？

- JsonStatham

---

你真的需要在表A中的列中存储这个值吗？通常最好不要存储可以重新计算的信息。你是否可以只将该列添加到针对这些表的查询中？ - Damien_The_Unbeliever

所以您不想使用子查询吗？ - Anri

表A中没有年份吗？ - RomanKonz

我可以在表A中添加年份，只是为了保持简单，我将其省略了，表A实际上是一个视图。 - JsonStatham

我认为OP只是希望新列的值在运行时被计算，而不是像存储一样“存储”。 - general exception

@SelectDistinct 我只是在想，因为似乎没有年份就会出问题;-) - RomanKonz

Answer 1

你可以直接使用它：

select *
from table a
    join table b
        on a.month = b.month
           and a.average between b.rangestart and isnull(b.rangeend,10000) -- 100000 = greater than max value

- RomanKonz

Answer 2

所以你真正想要的是

select a.*,b.colour from a 
left join table b on a.month=b.month 
 and ((b.rangeend is null and a.average>b.rangestart) 
    or (a.average between b.rangestart and b.rangeend))

我不能保证它能够正常工作，因为我没有时间输入一些表格和数据。

- BugFinder

Answer 3

如果您想要向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)

希望这能有所帮助 :)

- Jaime