多表关联生成目标表结果冗余求助：如何正确提取数据填充列？

现有两张数据表：表A（索引信息表）和表B（案件信息表），结构及数据如下：

表A结构与数据 #

RECORD_NUMBER   INDEXNO      SUFFIX    FILENO    YEAR      INDEXTYPE
-------------   -----------  ------    ------    -------   ---------
123382          4037                   1019      2004
123383          4038                   1019      2003
124814          3586                   1019      2005 
117912          2007                   1019      2007

表B结构与数据 #

RECORD_NUMBER   NOI_DATE     CALNO       CALTRACK_FILENO    FILENO    YEAR       CALTYPE
-------------   -----------  ---------   ---------------    ------    -------    ------- 
23421           2022-02-23   2022T0930   xxxxx              1019      1993       4  
24020           2022-02-23   2022T0931   xxxxx              1019      1994       4
14524           2022-02-23   2005T0631                      1019      2005       3

需要生成目标表C，结构与数据如下：

CALNO       INDEXNO    FILENO    YEAR    CALTYPE    INDEXTYPE
---------   -------    ------    ----    -------    ---------
2022T0930              1019      1993    4
2022T0931              1019      1994    4
            4038       1019      2003
            4037       1019      2004
2005T0631   3586       1019      2005    3
            2007       1019      2007 

但当前执行的SQL（如下）会生成12条记录（表A4条×表B3条的笛卡尔积），无法得到预期的6条记录：

select * 
from calno b
full join indexno a on (b.FILENO = a.FILENO)
where b.FILENO = 1019
order by a.INDEXNO

问题原因 #

原SQL仅通过FILENO关联两张表，而表A和表B中FILENO=1019的记录分别有4条和3条，宽松的关联条件导致所有记录两两匹配，产生笛卡尔积（4×3=12条）。

正确SQL实现 #

需要同时通过FILENO和YEAR进行关联，只有当两张表中同一文件编号且同一年份的记录才会合并，其余记录各自保留。同时使用COALESCE处理空值，确保FILENO和YEAR字段始终有值：

SELECT
    b.CALNO,
    a.INDEXNO,
    COALESCE(a.FILENO, b.FILENO) AS FILENO,
    COALESCE(a.YEAR, b.YEAR) AS YEAR,
    b.CALTYPE,
    a.INDEXTYPE
FROM
    表A a
FULL OUTER JOIN
    表B b
ON
    a.FILENO = b.FILENO
    AND a.YEAR = b.YEAR
WHERE
    COALESCE(a.FILENO, b.FILENO) = 1019
ORDER BY
    YEAR,
    CALNO DESC,
    INDEXNO DESC;

逻辑说明 #

1. 关联条件：a.FILENO = b.FILENO AND a.YEAR = b.YEAR，确保仅同文件、同年份的记录合并，避免笛卡尔积。
2. 空值处理：COALESCE(a.FILENO, b.FILENO)和COALESCE(a.YEAR, b.YEAR)保证即使某张表无匹配记录，这两个字段仍能取到有效值。
3. 排序：按YEAR升序，再按CALNO降序、INDEXNO降序排列，与目标表C的顺序一致。

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