表 B 范围内表 A 值的 SQL 连接
我有两个表格，可以在附图中看到.
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
相关文章