sql中无跨数据库兼容的full join写法，mysql 8.0.28前不支持，sql server虽支持但null匹配易致对账错误；正确做法是用left join+right join+union all手动模拟，并以trade_date±1天、abs(amount)容差比对、counterparty_id三条件组合join。
SQL里根本没有FULL JOIN的跨数据库兼容写法
绝大多数生产环境用的不是PostgreSQL，而是MySQL、SQL Server或Oracle——而MySQL直到8.0.28才支持FULL JOIN，且默认仍不启用；SQL Server虽支持，但实际对账场景中常因NULL匹配逻辑出错导致重复或漏对。真实项目里硬写FULL JOIN，大概率跑不通，或者对上一堆假账。
正确做法是用LEFT JOIN + RIGHT JOIN + UNION ALL手动模拟，核心是保证两边记录都不丢，且能区分来源：
- 左边表有、右边表无 → 视为“我方已记账，对方未入账”
- 右边表有、左边表无 → 视为“对方已记账，我方未入账”
- 两边都有但金额/日期不一致 → 视为“差异项”，需人工复核
对账主键不能只靠单字段，必须组合业务唯一键
用bill_id或order_no单独做JOIN条件？99%会翻车。实际账单中，同一笔支付可能拆成多笔分账，同一笔退款可能对应多个原订单，甚至存在手工补录单、冲正单等干扰项。
真正可用的JOIN条件至少包含三项：
-
trade_date（交易日期，误差容忍±1天，避免跨日批次问题） -
amount（金额绝对值相等，注意负号含义：收入/支出方向） -
counterparty_id（对手方标识，银行联行号、支付宝PID、微信MCHID等，不能只用名称模糊匹配）
示例片段（以PostgreSQL为例，其他库需微调）：
SELECT
a.bill_id AS our_bill_id,
b.bill_id AS their_bill_id,
a.amount AS our_amount,
b.amount AS their_amount,
CASE
WHEN a.bill_id IS NULL THEN 'missing_our'
WHEN b.bill_id IS NULL THEN 'missing_their'
WHEN ABS(a.amount - b.amount) > 0.01 THEN 'amount_mismatch'
ELSE 'matched'
END AS status
FROM our_bills a
FULL JOIN their_bills b
ON a.trade_date::date BETWEEN b.trade_date::date - 1 AND b.trade_date::date + 1
AND ABS(a.amount) = ABS(b.amount)
AND a.counterparty_id = b.counterparty_id;
金额比对必须处理浮点误差和符号逻辑
直接写a.amount = b.amount在金融场景等于埋雷。原因有三：
- 数据库存储用
DECIMAL，但中间计算可能转成FLOAT，产生微小误差（如99.99存成99.98999999999999） - 一方记收入（+），另一方记支出（−），符号相反但业务等价
- 手续费分摊、汇率折算导致金额天然存在分位差异
安全比对方式是：
- 统一取绝对值：
ABS(a.amount)和ABS(b.amount) - 用固定容差判断：
ABS(ABS(a.amount) - ABS(b.amount)) - 单独校验方向一致性：
sign(a.amount) = sign(b.amount)或按业务规则允许反向（如收付互抵）
自动对账结果必须保留原始行，不能只输出差异
很多团队一上来就WHERE status != 'matched'，只导出“未对齐”数据——这会导致无法回溯：某笔账单为什么没被匹配？是时间窗太窄？对手方ID映射错了？还是金额四舍五入规则不一致？
上线前必须确保输出含以下字段：
-
our_bill_id/their_bill_id（空值也要保留） -
our_amount/their_amount（原始值，不格式化） -
match_key（生成的组合键，如MD5(trade_date::text || '|' || ABS(amount)::text || '|' || counterparty_id)） -
status（明确标记missing_our/missing_their/amount_mismatch等）
否则每次排查都要重跑全量，根本没法定位到底是映射配置错了，还是上游数据本身缺数。
最易被忽略的一点：对账脚本里没有ORDER BY，不同执行顺序可能导致UNION ALL结果随机，人工核对时反复看到“同一笔账有时对得上、有时对不上”。务必在最终SELECT加ORDER BY our_bill_id, their_bill_id，让结果可重现。
