全外连接（Full Outer Join）仅匹配表A首次出现记录的实现问询

嘿，我来帮你搞定这个全外连接的需求！你想要的是：在基于ID、Date、Amount做全外连接时，只让表A中每个相同（ID、Date、Amount）分组里TimeRef最小的那条记录和表B的匹配项关联，同时保留表A里同组的其他记录，以及表B中没有匹配表A的记录对吧？

先明确你的需求对应的完整预期结果应该是这样：

ID   Date    Amount TimeRef ID   Date    Amount   UIQ
------------------------------------------------------
xx   29/Jan   10    123     xx   29/Jan   10    45678
xx   29/Jan   10    345     Null Null     Null    Null
Null Null     Null  Null     xy   29/Jan   10    45670

下面给你两种主流的实现方式，适配不同的数据库环境：

方式一：用窗口函数（推荐，适用于PostgreSQL、SQL Server、MySQL 8.0+等） #

首先给表A的每条记录按（ID、Date、Amount）分组，给每组内的记录按TimeRef升序编号，最小的TimeRef对应编号1，然后在连接时只让编号为1的记录匹配表B：

WITH ranked_A AS (
    SELECT 
        *,
        -- 给每个分组的记录按TimeRef升序编号，最小的TimeRef对应rn=1
        ROW_NUMBER() OVER (PARTITION BY ID, Date, Amount ORDER BY TimeRef ASC) AS rn
    FROM TableA
)
SELECT
    a.ID AS A_ID,
    a.Date AS A_Date,
    a.Amount AS A_Amount,
    a.TimeRef,
    b.ID AS B_ID,
    b.Date AS B_Date,
    b.Amount AS B_Amount,
    b.UIQ
FROM ranked_A a
FULL OUTER JOIN TableB b
    ON a.ID = b.ID 
    AND a.Date = b.Date 
    AND a.Amount = b.Amount
    AND a.rn = 1 -- 仅匹配表A中分组内TimeRef最小的记录
ORDER BY 
    COALESCE(a.ID, b.ID), -- 按ID排序，兼容Null情况
    COALESCE(a.Date, b.Date),
    COALESCE(a.Amount, b.Amount),
    a.TimeRef;

效果说明： #

• 表A中xx、29/Jan、10的两条记录里，TimeRef=123的那条（rn=1）会和表B的xx记录关联，UIQ显示45678
• 同组的TimeRef=345的记录（rn>1）不会匹配表B的记录，对应B侧字段全为Null
• 表B中的xy记录因为在表A中没有匹配的（ID、Date、Amount），所以A侧字段全为Null

方式二：兼容老版本MySQL（不支持窗口函数的情况） #

如果你的MySQL版本低于8.0，没法用窗口函数，可以先用子查询找出每个分组的最小TimeRef，再做连接：

-- 先获取表A每个分组的最小TimeRef
WITH min_time_ref_A AS (
    SELECT ID, Date, Amount, MIN(TimeRef) AS min_TimeRef
    FROM TableA
    GROUP BY ID, Date, Amount
)
-- 先关联表A和最小TimeRef标记，再用UNION模拟全外连接
SELECT
    a.ID AS A_ID,
    a.Date AS A_Date,
    a.Amount AS A_Amount,
    a.TimeRef,
    b.ID AS B_ID,
    b.Date AS B_Date,
    b.Amount AS B_Amount,
    b.UIQ
FROM TableA a
LEFT JOIN min_time_ref_A m
    ON a.ID = m.ID 
    AND a.Date = m.Date 
    AND a.Amount = m.Amount 
    AND a.TimeRef = m.min_TimeRef
LEFT JOIN TableB b
    ON m.ID = b.ID 
    AND m.Date = b.Date 
    AND m.Amount = b.Amount
UNION ALL
-- 补充表B中没有匹配表A的记录
SELECT
    NULL AS A_ID,
    NULL AS A_Date,
    NULL AS A_Amount,
    NULL AS TimeRef,
    b.ID AS B_ID,
    b.Date AS B_Date,
    b.Amount AS B_Amount,
    b.UIQ
FROM TableB b
WHERE NOT EXISTS (
    SELECT 1 FROM TableA a
    WHERE a.ID = b.ID 
    AND a.Date = b.Date 
    AND a.Amount = b.Amount
)
ORDER BY 
    COALESCE(A_ID, B_ID),
    COALESCE(A_Date, B_Date),
    COALESCE(A_Amount, B_Amount),
    TimeRef;

备注：内容来源于stack exchange，提问作者theBean