問題描述

我有兩個表格，可以在附圖中看到.

I have two tables that can be seen in accompanying image.

表 A 包含部門、月份和平均值.

表 B 包含 月、年、RangeStart、RangeEnd 和 <強>顏色.

Table B contains Month, Year, RangeStart, RangeEnd and Colour.

如果您查看表 B 的屏幕截圖，您會看到每個月都有綠色、黃色、橙色和紅色值.你也有一個范圍.

If you look at the screen shot of Table B, you will see for each Month you have a Green, Yellow, Orange and Red value. You also have a range.

我需要什么......

我需要在表 A 上新建一個名為顏色"的列.在此列中，我需要綠色、黃色、橙色或紅色.為月份分配哪種顏色的決定因素將是平均"列.

I need a new column on Table A named 'Colour'. In this column, I need either Green, Yellow, Orange or Red. The deciding factor on which colour is assigned to the month will be the 'Average' column.

例如:

DepartmentA 的 5 月平均值等于 0.96在引用表 B 時，我可以看到第 8 行，0.75+ 將是它適合的范圍.因此，紅色是我想放在 表 A 中與 Mays 平均值相鄰的顏色.

DepartmentA for May's Average is equal to 0.96 Upon referencing Table B, I can see that line 8, 0.75+ will be the range this fits into. Therefore Red is the colour I want placed in table A next to Mays average.

我已將每月最高范圍的 RangeEnd 保留為 NULL，因為它基本上是 75+，任何大于 0.75 的位置都在此處.

I have left RangeEnd for the highest range per month as NULL as it is basically 75+, anything greater than 0.75 slots in here.

誰能指出我正確的方向，而且不會太耗時.

Can anyone point me in the right direction that is not too time consuming.

推薦答案

你可以直接使用:

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

這篇關于表 B 范圍內表 A 值的 SQL 連接的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！