如何基于类别与值范围从另一表获取对应积分

需求说明 #

需要将主表（Sales）中的Revenue、Profit数值，与积分表（Point）的区间规则比对，生成对应的Revenuepoint、Profitpoint积分列。匹配规则如下：

• 必须匹配Department和BU
• Revenuepoint对应积分表中Category='Revenue'的区间规则
• Profitpoint对应积分表中Category='Profit'的区间规则

主表（Sales） #

积分表（Point） #

预期结果表 #

问题代码分析 #

你尝试的代码缺少Department、BU和Category的匹配条件，会导致区间匹配错误，同时重复定义了Revenuepoint列，存在语法问题：

SELECT 
a.User, 
a.Department, 
a.BU, 
a.Revenue, 
(SELECT MAX(b.Point) from Point b WHERE a.Revenue BETWEEN b.fromvalue and b.tovalue) as Revenuepoint,
b.Point as Revenuepoint,
a.Profit, 
(SELECT MAX(b.Point) from Point b WHERE a.Profit BETWEEN b.fromvalue and b.tovalue) as Profitpoint
FROM Sales a

正确解决方案 #

方案1：关联子查询 #

针对每个积分列，在子查询中加上完整的匹配条件，确保只匹配对应分类、部门和BU的区间规则：

SELECT 
    a.User,
    a.Department,
    a.BU,
    a.Revenue,
    (
        SELECT b.Point
        FROM Point b
        WHERE b.Category = 'Revenue'
          AND a.Department = b.Department
          AND a.BU = b.BU
          AND a.Revenue BETWEEN b.Fromvalue AND b.Tovalue
    ) AS Revenuepoint,
    a.Profit,
    (
        SELECT b.Point
        FROM Point b
        WHERE b.Category = 'Profit'
          AND a.Department = b.Department
          AND a.BU = b.BU
          AND a.Profit BETWEEN b.Fromvalue AND b.Tovalue
    ) AS Profitpoint
FROM Sales a;

方案2：LEFT JOIN关联 #

通过两次LEFT JOIN分别关联Revenue和Profit的积分规则，逻辑更直观：

SELECT 
    a.User,
    a.Department,
    a.BU,
    a.Revenue,
    rev.Point AS Revenuepoint,
    a.Profit,
    prof.Point AS Profitpoint
FROM Sales a
LEFT JOIN Point rev 
    ON rev.Category = 'Revenue'
    AND a.Department = rev.Department
    AND a.BU = rev.BU
    AND a.Revenue BETWEEN rev.Fromvalue AND rev.Tovalue
LEFT JOIN Point prof 
    ON prof.Category = 'Profit'
    AND a.Department = prof.Department
    AND a.BU = prof.BU
    AND a.Profit BETWEEN prof.Fromvalue AND prof.Tovalue;

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