CASE语句关联两表返回多记录且结果错误问题求助

你的SQL出现返回多行、结果不符合预期的问题，主要是连接逻辑和CASE语句的两处关键问题导致的，我来逐一拆解并给出修复方案：

1. 核心问题：内连接引发的笛卡尔积 #

你用inner join将ORDERS和BUCKET_TABLE按Org关联，这会把单个订单记录和该Org下的所有Bucket记录逐一匹配。如果BUCKET_TABLE里有4条Bucket数据，自然会返回4行结果——这就是你看到4行的直接原因。

2. CASE语句的两处错误 #

• 笔误问题：代码里的0.REVENUE是明显的输入错误，应该写成o.REVENUE（把表别名o写成了数字0）。
• 逻辑缺陷：你的CASE条件只判断了REVENUE <= b.LIMIT，但没有限定“大于上一个Bucket的上限”。假设Bucket的LIMIT是递增的（比如Bucket1上限100、Bucket2上限200、Bucket3上限300），那么收入250的订单只会满足Bucket3的条件，但如果你的Bucket区间定义是其他逻辑，这种单一条件会导致匹配错误。更关键的是，因为连接后每个Bucket行都会执行一次CASE，所以每条匹配行都会生成一个DERIVED_BUCKET值，而非唯一正确的那个。

修复方案 #

方法1：用子查询精准匹配唯一Bucket #

假设你的BUCKET_TABLE中每个Org的Bucket是按上限递增的区间（比如Bucket1: ≤100，Bucket2: 101-200，Bucket3:201-300，Bucket4:>300），可以通过子查询找到唯一符合条件的Bucket：

SELECT 
    o.Org, 
    o.REVENUE,
    b.BUCKET,
    CASE b.BUCKET
        WHEN 'Bucket 1' THEN '1'
        WHEN 'Bucket 2' THEN '2'
        WHEN 'Bucket 3' THEN '3'
        ELSE '4'
    END AS DERIVED_BUCKET
FROM ORDERS o
INNER JOIN BUCKET_TABLE b 
    ON o.Org = b.Org
WHERE o.ID = '12345'
-- 筛选出：收入<=当前Bucket上限，且不存在更小的上限也能容纳该收入的Bucket
AND NOT EXISTS (
    SELECT 1 
    FROM BUCKET_TABLE b2 
    WHERE b2.Org = o.Org
    AND b2.LIMIT >= o.REVENUE
    AND b2.LIMIT < b.LIMIT
)

方法2：用窗口函数简化匹配逻辑 #

用窗口函数为每个Org的Bucket按上限排序，直接取匹配的唯一Bucket：

WITH ranked_buckets AS (
    SELECT 
        Org,
        BUCKET,
        LIMIT,
        -- 按上限从大到小排序，这样符合条件的Bucket会排在第一位
        ROW_NUMBER() OVER (PARTITION BY Org ORDER BY LIMIT DESC) AS rn
    FROM BUCKET_TABLE
)
SELECT 
    o.Org,
    o.REVENUE,
    rb.BUCKET,
    CASE rb.BUCKET
        WHEN 'Bucket 1' THEN '1'
        WHEN 'Bucket 2' THEN '2'
        WHEN 'Bucket 3' THEN '3'
        ELSE '4'
    END AS DERIVED_BUCKET
FROM ORDERS o
INNER JOIN ranked_buckets rb 
    ON o.Org = rb.Org
    AND o.REVENUE <= rb.LIMIT
WHERE o.ID = '12345'
AND rb.rn = 1 -- 取最大的、能容纳该收入的Bucket

方法3：直接通过子查询计算Bucket（无需关联整张表） #

如果只需要得到DERIVED_BUCKET和对应的Bucket名称，可以完全避免表连接，用子查询直接取值：

SELECT 
    o.Org,
    o.REVENUE,
    CASE
        WHEN o.REVENUE <= (SELECT LIMIT FROM BUCKET_TABLE WHERE Org = o.Org AND BUCKET = 'Bucket 1') THEN '1'
        WHEN o.REVENUE <= (SELECT LIMIT FROM BUCKET_TABLE WHERE Org = o.Org AND BUCKET = 'Bucket 2') THEN '2'
        WHEN o.REVENUE <= (SELECT LIMIT FROM BUCKET_TABLE WHERE Org = o.Org AND BUCKET = 'Bucket 3') THEN '3'
        ELSE '4'
    END AS DERIVED_BUCKET,
    -- 同时获取对应的Bucket名称
    (SELECT BUCKET FROM BUCKET_TABLE WHERE Org = o.Org AND o.REVENUE <= LIMIT ORDER BY LIMIT DESC LIMIT 1) AS BUCKET
FROM ORDERS o
WHERE o.ID = '12345'

关键注意事项 #

• 先修正笔误：把0.REVENUE改为o.REVENUE；
• 确保BUCKET_TABLE中每个Org的Bucket区间是连续且不重叠的，否则可能出现匹配不到或匹配多个的情况；
• 避免无筛选的表连接：直接关联订单和所有Bucket会产生笛卡尔积，必须通过子查询、窗口函数或条件筛选找到唯一匹配的Bucket。

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