如何在Oracle SQL中基于列的最值关联两张表记录？

我有两张表table_A和table_B，结构及数据如下：

table_A #

table_B #

需求说明 #

• 按dt_trans分组，获取table_A中每组的最高和最低amnt值；
• 对于同一ref，将table_B中最高anuity_amnt对应的policy分配给table_A同组的最高amnt记录，最低anuity_amnt对应的policy分配给最低amnt记录，最终生成目标表table_C（结构及示例数据如下）：

table_C #

我目前只尝试了按dt_trans对table_A进行初步处理，但没找到完整解决方案，尝试的代码如下：

SELECT 
      ref
      ,amnt
      ,dt_trans OVER (PARTITION BY dt_trans) 
 FROM 
      table_A;

要实现需求，可分三步处理：标记表A的极值记录、提取表B的极值对应policy、关联表分配policy，完整SQL代码如下：

WITH ranked_a AS (
    -- 标记table_A中每组的最高、最低amnt记录
    SELECT 
        ref,
        amnt,
        dt_trans,
        -- 按ref+dt_trans分组，amnt降序排，第一即为组内最高值
        ROW_NUMBER() OVER (PARTITION BY ref, dt_trans ORDER BY amnt DESC) AS rn_high,
        -- 按ref+dt_trans分组，amnt升序排，第一即为组内最低值
        ROW_NUMBER() OVER (PARTITION BY ref, dt_trans ORDER BY amnt ASC) AS rn_low
    FROM table_A
),
policy_extremes AS (
    -- 获取每个ref对应的最高、最低anuity_amnt的policy
    SELECT 
        ref,
        FIRST_VALUE(policy) OVER (PARTITION BY ref ORDER BY anuity_amnt DESC) AS high_policy,
        FIRST_VALUE(policy) OVER (PARTITION BY ref ORDER BY anuity_amnt ASC) AS low_policy
    FROM table_B
    -- 去重，每个ref仅保留一条极值记录
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ref ORDER BY anuity_amnt) = 1
)
-- 关联结果，根据标记分配对应policy
SELECT 
    ra.ref,
    ra.amnt,
    ra.dt_trans,
    CASE
        WHEN ra.rn_high = 1 THEN pe.high_policy
        WHEN ra.rn_low = 1 THEN pe.low_policy
        ELSE NULL
    END AS policy
FROM ranked_a ra
JOIN policy_extremes pe ON ra.ref = pe.ref
ORDER BY ra.dt_trans DESC, ra.amnt DESC;

代码说明 #

• ranked_a CTE：通过两个窗口函数分别标记每组的最高（rn_high=1）和最低（rn_low=1）amnt记录；
• policy_extremes CTE：对每个ref提取最高、最低年金对应的policy，通过QUALIFY去重确保每个ref仅返回一条记录；
• 最后关联两个CTE，用CASE语句根据极值标记分配对应policy。

如果你的SQL方言不支持QUALIFY（如MySQL），可改用子查询去重：

WITH ranked_a AS (
    SELECT 
        ref,
        amnt,
        dt_trans,
        ROW_NUMBER() OVER (PARTITION BY ref, dt_trans ORDER BY amnt DESC) AS rn_high,
        ROW_NUMBER() OVER (PARTITION BY ref, dt_trans ORDER BY amnt ASC) AS rn_low
    FROM table_A
),
policy_extremes AS (
    SELECT 
        ref,
        MAX(CASE WHEN rn_high_p = 1 THEN policy END) AS high_policy,
        MAX(CASE WHEN rn_low_p = 1 THEN policy END) AS low_policy
    FROM (
        SELECT 
            ref,
            policy,
            ROW_NUMBER() OVER (PARTITION BY ref ORDER BY anuity_amnt DESC) AS rn_high_p,
            ROW_NUMBER() OVER (PARTITION BY ref ORDER BY anuity_amnt ASC) AS rn_low_p
        FROM table_B
    ) t
    WHERE rn_high_p = 1 OR rn_low_p = 1
    GROUP BY ref
)
SELECT 
    ra.ref,
    ra.amnt,
    ra.dt_trans,
    CASE
        WHEN ra.rn_high = 1 THEN pe.high_policy
        WHEN ra.rn_low = 1 THEN pe.low_policy
        ELSE NULL
    END AS policy
FROM ranked_a ra
JOIN policy_extremes pe ON ra.ref = pe.ref
ORDER BY ra.dt_trans DESC, ra.amnt DESC;

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