如何通过三表关联筛选需同步至wallet表的inward交易

现有业务数据表 #

1. inward表 #

包含session_id、beneficiary_account_no、amount、transaction_date字段，数据如下：

2. wallet表 #

包含session_id、to_account_no、amount、time字段，数据如下：

3. pending_wallet表 #

包含session_id、amount、to_account_no、created_date、status字段，数据如下：

需求说明 #

需要编写SQL筛选出符合以下条件的记录（以session_id=6为例）：

• 记录存在于inward表，但不存在于wallet表
• 在pending_wallet表中对应的status为'00'（'00'代表交易成功）

最终目标是：将inward表中所有**未在pending_wallet表中存在，或存在但status为'00'**的交易同步至wallet表。

原始错误SQL #

SELECT session_id FROM INWARD WHERE beneficiary_account_no LIKE '460%'
AND transaction_date > '2024-06-06' AND transaction_date < '2024-06-08 00:00:30'
from inward
left join pending_wallet on inward.SESSION_ID =  pending_wallet.session_id
left join wallet on inward.SESSION_ID =  wallet.session_id
where pending_wallet.SESSION_ID is NULL AND pending_wallet.session_id = '00';

修正后的SQL及问题解析 #

原始SQL的问题 #

1. 语法错误：重复使用FROM子句，先写SELECT ... FROM INWARD WHERE ...后又重复写from inward，直接导致SQL执行报错。
2. 逻辑矛盾：同时判断pending_wallet.SESSION_ID is NULL和pending_wallet.session_id = '00'，两个条件不可能同时成立，逻辑完全冲突。
3. 条件覆盖不全：没体现需求中“未存在于pending_wallet表，或存在但status为'00'”的核心逻辑，也没正确筛选出“不存在于wallet表”的记录。

修正后的SQL #

SELECT 
    i.session_id
FROM 
    inward i
LEFT JOIN 
    wallet w ON i.session_id = w.session_id
LEFT JOIN 
    pending_wallet pw ON i.session_id = pw.session_id
WHERE 
    -- 筛选账户前缀为460的交易
    i.beneficiary_account_no LIKE '460%'
    -- 交易时间范围
    AND i.transaction_date > '2024-06-06' 
    AND i.transaction_date < '2024-06-08 00:00:30'
    -- 记录不存在于wallet表
    AND w.session_id IS NULL
    -- 满足：要么不在pending_wallet表，要么在且status为'00'
    AND (pw.session_id IS NULL OR pw.status = '00');

逻辑说明 #

• 用LEFT JOIN关联wallet表，通过w.session_id IS NULL筛选出inward表中未同步到wallet的记录。
• 关联pending_wallet表后，通过(pw.session_id IS NULL OR pw.status = '00')匹配需求中两种需要同步的情况：要么交易不在pending_wallet表，要么存在且交易成功。
• 保留了原始SQL中的账户前缀和交易时间筛选条件，确保只处理指定范围的记录。

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