One SQL query that sorts reconciliation gaps
Added 2026-10-29
One SQL query that sorts reconciliation gaps — Chinese original prompt
你是做数据核对的工程师。要核对的是 [业务表：订单/退款/结算] 和 [来源表：支付流水/渠道账单]，主键是 [字段]，金额字段是 [字段]，两边各 [行数]，总额相差 [金额]。请：1）写一条可执行的 SQL，先做全外连接按主键比对，把差异分成漏记、重复、金额不等、状态不一致四类，并给出每类的行数与金额合计；2）每条查询都要能单独跑，说明在 [MySQL/PG/数仓] 下怎么写才不报错；3）给出定位顺序：先看哪类占比最大，再看时区、汇率、四舍五入、单号复用造成的假差异；4）对每类差异给下一步动作，是回补、冲销还是人工核；5）写清结果怎么留档，附上口径说明。验收：四类金额合计加上一致部分能还原两边总额，差一分钱也能解释。
Site original
Chinese original
Replace these placeholders 6
Swap the bracketed parts for your own details before sending.
[业务表：订单/退款/结算][来源表：支付流水/渠道账单][字段][行数][金额][MySQL/PG/数仓]
How to use
先跑总额再跑明细，总额对上了也可能有错行。
Try tweaking it
- Have it restate requirements and constraints before writing code.
- Ask for the smallest change plus a rollback path, not a full-file rewrite.
What you should get
- A minimal, runnable change with copy-ready code
- Impact list: which files and call sites must follow
Common mistakes
- Asking it to touch ten files at once, then losing track of what broke what.
- Pasting only the error, with no code or version, and getting an unfixable answer.
How to check the output
- The change runs, can be rolled back, and touches nothing unrelated.
- Edge cases (empty, oversized, concurrent) are covered or explained.
Where it works
- Good for: having it restate requirements and constraints, then propose the smallest runnable change.
- Watch out: include versions and a minimal reproduction with the error, or the fix may look plausible but not fit.