基于SEC字段按优先级关联TableA与TableB的SQL查询需求

我有两个SQL表 TableA 和 TableB，表结构及数据如下：

TableA：

AgentID    SEC      CUS
-------    ----     ------
22441      138      41332
53321      59       29296
89952      282      89011
68897      297      77654

TableB：

AgentID    SEC    STAT   NIN
-------    ----   -----  ------
22441      138     C     56428
22441      138           66543
53321      52      C     77532
53321      59            87666       
89952      282     C     00988
89952      281           87776
68897      297           11222

需要关联 TableA 和 TableB，获取 TableA 的所有列以及 TableB 的 NIN 列，需遵循以下规则：

• 仅通过 SEC 字段进行关联；
• 每个 AgentID 仅关联 TableB 中的一行数据；
• 优先关联 TableB 中 STAT = 'C' 且 SEC 匹配的行；
• 若不存在符合上述条件的行，则关联同一 AgentID 下 SEC 匹配的另一行；
• 若 TableB 中该 AgentID 仅有一行，则直接关联该行。

示例：AgentID 22441取 STAT='C' 行的 NIN；AgentID 53321因 STAT='C' 行 SEC 不匹配，取空白 STAT 行的 NIN。

期望输出：

AgentID    SEC      CUS     NIN
-------    ----     ------  ------
22441      138      41332   56428
53321      59       29296   87666        
89952      282      89011   00988
68897      297      77654   11222

注意：不能通过 AgentID 关联两表，仅能使用 SEC 字段。

解决方案 #

可以通过窗口函数对 TableB 的数据按优先级排序，再与 TableA 关联，确保每个 AgentID+SEC 组合只取符合规则的最高优先级行：

WITH ranked_table_b AS (
    SELECT 
        AgentID,
        SEC,
        NIN,
        -- 标记优先级：STAT='C'的行排第1，其他行排第2
        ROW_NUMBER() OVER (
            PARTITION BY AgentID, SEC 
            ORDER BY CASE WHEN STAT = 'C' THEN 1 ELSE 2 END
        ) AS row_rank
    FROM TableB
)
SELECT 
    a.AgentID,
    a.SEC,
    a.CUS,
    b.NIN
FROM TableA a
LEFT JOIN ranked_table_b b 
    ON a.SEC = b.SEC 
    AND a.AgentID = b.AgentID
    AND b.row_rank = 1;

思路说明 #

1. 预处理TableB：用 ROW_NUMBER() 窗口函数按 AgentID 和 SEC 分组，对每组内的行按 STAT='C' 优先的规则排序，生成优先级序号 row_rank；
2. 关联查询：将 TableA 与预处理后的 TableB 关联，仅保留每个组合下优先级最高（row_rank=1）的行，核心关联条件为 SEC 字段匹配，同时筛选同一 AgentID 的行确保数据准确性。

如果使用不支持CTE的数据库，也可以用子查询实现：

SELECT 
    a.AgentID,
    a.SEC,
    a.CUS,
    (
        SELECT TOP 1 NIN
        FROM TableB b
        WHERE b.SEC = a.SEC 
          AND b.AgentID = a.AgentID
        ORDER BY CASE WHEN b.STAT = 'C' THEN 1 ELSE 2 END
    ) AS NIN
FROM TableA a;

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