如何在SQL中为每条记录匹配另一张表的最接近值？

要实现每个ID对应Table2中ENTRY_VALUE最接近Table1目标值的记录，你可以借助窗口函数来完成排序和筛选，具体方案如下：

基础解决方案（取唯一最接近记录） #

WITH ranked_records AS (
    SELECT 
        t2.ID,
        t2.ENTRY_VALUE,
        t2.STATUS,
        -- 计算当前记录与目标值的差值绝对值
        ABS(t2.ENTRY_VALUE - t1.ENTRY_VALUE) AS value_diff,
        -- 按ID分组，根据差值从小到大排名，最小差值的记录排第1
        ROW_NUMBER() OVER (PARTITION BY t2.ID ORDER BY ABS(t2.ENTRY_VALUE - t1.ENTRY_VALUE)) AS rank_num
    FROM Table2 t2
    INNER JOIN Table1 t1 ON t2.ID = t1.ID
)
-- 筛选出每个ID下排名第1的记录
SELECT ID, ENTRY_VALUE, STATUS
FROM ranked_records
WHERE rank_num = 1;

代码说明 #

1. CTE临时表ranked_records：

   • 通过INNER JOIN关联两张表的ID，确保只处理匹配的记录；
   • 用ABS()计算Table2记录与Table1对应目标值的差值绝对值，差值越小说明越接近；
   • ROW_NUMBER()窗口函数按ID分组，根据差值升序排序，给每条记录分配一个排名，差值最小的记录会得到排名1。

2. 主查询：从临时表中筛选出排名为1的记录，就是每个ID下最接近目标值的那条。

处理差值相同的情况 #

如果某个ID下存在多条记录与目标值的差值完全相同，ROW_NUMBER()只会随机返回其中一条。若想保留所有差值最小的记录，只需将ROW_NUMBER()替换为RANK()：

WITH ranked_records AS (
    SELECT 
        t2.ID,
        t2.ENTRY_VALUE,
        t2.STATUS,
        ABS(t2.ENTRY_VALUE - t1.ENTRY_VALUE) AS value_diff,
        RANK() OVER (PARTITION BY t2.ID ORDER BY ABS(t2.ENTRY_VALUE - t1.ENTRY_VALUE)) AS rank_num
    FROM Table2 t2
    INNER JOIN Table1 t1 ON t2.ID = t1.ID
)
SELECT ID, ENTRY_VALUE, STATUS
FROM ranked_records
WHERE rank_num = 1;

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