Oracle SQL：如何基于数量匹配关联TABLE A、B、C三张表

问题背景 #

现有三个表，A表为基础ID表，B表和C表通过A表的ID关联，B、C表无直接关联。需要将B、C表的条目按ID对应展示，排查数据缺失（正常情况下同一ID下B、C条目数应一致，实际常存在某方缺失）。使用普通外连接会产生过多冗余行，需实现每行仅匹配一次的关联效果。

表结构 #

TABLE A #

TABLE B #

TABLE C #

预期查询结果 #

当ID = 1时 #

当ID = 2时 #

当ID = 3时 #

当ID = 4时 #

当ID = 5时 #

解决方案 #

核心思路是为B、C表中同一ID下的行添加行号，然后通过ID和行号进行关联，确保每行仅匹配一次，多余的行显示null。以下是通用SQL实现（兼容MySQL 8.0+、PostgreSQL、SQL Server等主流关系型数据库）：

WITH b_with_row AS (
    SELECT 
        ID, 
        VALUE_B, 
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE_B) AS row_num
    FROM TABLE_B
),
c_with_row AS (
    SELECT 
        ID, 
        VALUE_C, 
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE_C) AS row_num
    FROM TABLE_C
),
all_rows AS (
    SELECT ID, row_num FROM b_with_row
    UNION
    SELECT ID, row_num FROM c_with_row
)
SELECT 
    ar.ID,
    bw.VALUE_B,
    cw.VALUE_C
FROM all_rows ar
LEFT JOIN b_with_row bw ON ar.ID = bw.ID AND ar.row_num = bw.row_num
LEFT JOIN c_with_row cw ON ar.ID = cw.ID AND ar.row_num = cw.row_num
ORDER BY ar.ID, ar.row_num;

说明 #

1. ROW_NUMBER()函数为每个ID下的行生成连续序号，排序规则可根据实际需求调整（比如替换为数据插入时间字段，若存在）。
2. all_rows通过UNION获取所有ID和行号的组合，确保不会遗漏任何一方的条目。
3. 最后通过LEFT JOIN关联两个带行号的表，即可得到预期的一一对应结果。

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