关联查询获取与result表score匹配的reference表value值异常问题

数据表结构 #

reference表 #

id  | score  | value | type_id
1   | 0      | 10    | 1
2   | 1      | 20    | 1
3   | 2      | 30    | 1
..  | ..     | ..    | ..

result表 #

id  | score  | type_id
1   | 2      | 1
2   | 7      | 2
3   | 0      | 3

需求 #

根据result表的score值，为每个type_id从reference表匹配对应value，规则：

• 若result的score在同type_id的reference表中存在，直接取该score对应的value；
• 若result的score大于同type_id的reference表所有score，取该type_id下reference表最大score对应的value。

当前使用的查询语句 #

SELECT ref.score, ref.value, ref.type_id
FROM `refernce` ref
JOIN `result` res
    ON ref.type_id = res.type_id
WHERE res.score >= ref.score
GROUP BY ref.type_id
ORDER BY ref.id DESC;

输出对比 #

预期输出 #

score | value   | type_id
0     | 8       | 3
3     | 25      | 2
2     | 30      | 1

实际输出 #

score | value   | type_id
0     | 8       | 3
0     | 5       | 2
0     | 10      | 1

问题分析 #

原查询的核心问题是GROUP BY ref.type_id后，未指定筛选符合条件的最大score记录，数据库默认返回分组后的第一条记录（同type_id下score最小的那条），导致实际输出全部取了score=0对应的value。

解决方案 #

方法一：子查询匹配目标score #

先通过子查询找到每个type_id对应的目标score（即不大于result.score的最大reference.score），再关联reference表获取对应value：

SELECT 
    ref.score, 
    ref.value, 
    res.type_id
FROM result res
JOIN (
    SELECT 
        res.type_id,
        MAX(ref.score) AS target_score
    FROM result res
    LEFT JOIN reference ref 
        ON ref.type_id = res.type_id 
        AND ref.score <= res.score
    GROUP BY res.type_id
) t ON res.type_id = t.type_id
LEFT JOIN reference ref 
    ON ref.type_id = res.type_id 
    AND ref.score = t.target_score
ORDER BY res.id DESC;

方法二：窗口函数实现（适用于MySQL 8+及支持窗口函数的数据库） #

利用窗口函数筛选出每个type_id下符合条件的最大score记录：

SELECT DISTINCT
    FIRST_VALUE(ref.score) OVER (
        PARTITION BY res.type_id 
        ORDER BY ref.score DESC
    ) AS score,
    FIRST_VALUE(ref.value) OVER (
        PARTITION BY res.type_id 
        ORDER BY ref.score DESC
    ) AS value,
    res.type_id
FROM result res
LEFT JOIN reference ref 
    ON ref.type_id = res.type_id 
    AND ref.score <= res.score
ORDER BY res.id DESC;

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