如何基于关联表查询并按最近税额值分组交易记录？

架构老林

2026-6-18

解决按rollno关联最接近tax值的SQL查询问题

现有表结构

表A

id	policy	rollno	tax
1	P1	R1	60
2	P2	R1	30
3	P3	R2	120
4	P4	R2	35

表B

id	amnt	rollno
1	30	R1
2	140	R1
3	90	R2
4	65	R2
5	20	R2
6	200	R2
7	130	R2
8	85	R2

需求说明

每个rollno对应多个policy和多笔交易，需要编写SQL查询，将表B的交易按对应rollno下表A中最接近的tax值关联对应policy，得到如下期望结果：

期望结果（表C）

id	amnt	rollno	policy	tax
1	30	R1	P2	30
2	140	R1	P1	60
3	90	R2	P3	120
4	65	R2	P3	120
5	20	R2	P4	35
6	200	R2	P3	120
7	130	R2	P3	120
8	85	R2	P3	120

问题分析

你当前尝试的SQL通过UNION关联最大/最小tax，但会产生重复记录，核心原因是没有针对每个交易记录筛选出唯一的最接近tax的policy，而是把所有可能的tax都做了关联，导致结果冗余。

解决方案

我们可以通过计算每个交易金额与对应rollno下所有tax的差值绝对值，再用窗口函数为每个交易记录筛选出差值最小的关联记录：

WITH ranked_matches AS (
    SELECT 
        b.id,
        b.amnt,
        b.rollno,
        a.policy,
        a.tax,
        -- 计算金额与tax的差值绝对值
        ABS(b.amnt - a.tax) AS diff,
        -- 按交易ID分组，每个交易下按差值升序排序，差值相同时可指定tax的优先级
        ROW_NUMBER() OVER (
            PARTITION BY b.id 
            ORDER BY ABS(b.amnt - a.tax) ASC, a.tax DESC
        ) AS rn
    FROM tableB b
    JOIN tableA a ON b.rollno = a.rollno
)
SELECT id, amnt, rollno, policy, tax
FROM ranked_matches
WHERE rn = 1; -- 只保留差值最小的那条关联记录

方案说明

CTE ranked_matches：将表B和表A按rollno关联，计算每个交易金额与tax的差值绝对值，再用ROW_NUMBER()为每个交易记录（按b.id分组）的关联结果排序，差值最小的记录会被标记为rn=1。
筛选最终结果：通过WHERE rn = 1过滤，只保留每个交易对应的最接近tax的policy记录。