如何通过值与区间最值匹配从另一表返回对应评分类别

需要实现的功能：将主表（Main table）中的数值与Point表的区间（From到To）匹配，返回对应的评分字段。主表包含User、Department、BU、Revenue、Profit等字段，需填充Revenuepoint和Profitpoint；Point表定义了不同Category（Revenue/Profit）、Department、BU对应的数值区间及评分Point。

示例表结构 #

主表（Main table） #

--------------------------------------------------------------------------------
| User   | Department |  BU  |  Revenue | Revenuepoint | Profit |  Profitpoint |
--------------------------------------------------------------------------------
| A      | 1000000    | 101  |  400     |              | 200    |              |
| B      | 1000001    | 101  |  300     |              | 100    |              |
| C      | 1000000    | 102  |  350     |              | 150    |              |
--------------------------------------------------------------------------------

Point表 #

---------------------------------------------------------
| Category| Department |  BU  |  From  | To    | Point  |
---------------------------------------------------------
| Revenue | 1000000    | 101  |  0     | 200   | 1      |
| Revenue | 1000000    | 101  |  201   | 400   | 2      |
| Revenue | 1000000    | 102  |  0     | 300   | 1      |
| Revenue | 1000000    | 102  |  301   | 400   | 2      |
| Revenue | 1000001    | 101  |  0     | 200   | 1      |
| Revenue | 1000001    | 101  |  201   | 400   | 2      |
| Profit  | 1000000    | 101  |  0     | 100   | 1      |
| Profit  | 1000000    | 101  |  101   | 300   | 2      |
| Profit  | 1000000    | 102  |  0     | 50    | 1      |
| Profit  | 1000000    | 102  |  51    | 200   | 2      |
| Profit  | 1000001    | 101  |  0     | 50    | 1      |
| Profit  | 1000001    | 101  |  51    | 200   | 2      |
---------------------------------------------------------

期望结果表 #

--------------------------------------------------------------------------------
| User   | Department |  BU  |  Revenue | Revenuepoint | Profit |  Profitpoint |
--------------------------------------------------------------------------------
| A      | 1000000    | 101  |  400     |       2      | 200    |       2      |
| B      | 1000001    | 101  |  300     |       2      | 40     |       1      |
| C      | 1000000    | 102  |  350     |       2      | 150    |       2      |
--------------------------------------------------------------------------------

尝试的代码 #

SELECT a.User, a.Department, a.BU, a.Revenue, b.Point as Revenuepoint
FROM Sales a
JOIN Point b ON a.Revenue BETWEEN b.fromvalue and b.tovalue

解决方案 #

你的代码缺少维度匹配条件（Department、BU、Category），也未处理Profit字段的评分填充。可通过两次关联Point表分别获取Revenue和Profit的评分：

SELECT 
    m.User,
    m.Department,
    m.BU,
    m.Revenue,
    rp.Point AS Revenuepoint,
    m.Profit,
    pp.Point AS Profitpoint
FROM Main_table m
-- 关联Revenue对应的评分规则
LEFT JOIN Point rp 
    ON m.Department = rp.Department
    AND m.BU = rp.BU
    AND rp.Category = 'Revenue'
    AND m.Revenue BETWEEN rp.From AND rp.To
-- 关联Profit对应的评分规则
LEFT JOIN Point pp 
    ON m.Department = pp.Department
    AND m.BU = pp.BU
    AND pp.Category = 'Profit'
    AND m.Profit BETWEEN pp.From AND pp.To;

关键说明 #

• 用LEFT JOIN保证主表所有记录都能返回，若业务要求必须匹配到评分规则，可替换为INNER JOIN
• 每个关联需同时匹配Department、BU和Category，避免跨维度匹配错误区间
• 使用BETWEEN判断数值是否落在Point表的From/To区间内，确保匹配正确评分

内容的提问来源于stack exchange，提问作者John Marston