表 B 范围内表 A 值的 SQL 连接

SQL Join on Table A value within Table B range(表 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

这篇关于表 B 范围内表 A 值的 SQL 连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！