销售订单和开票金额对不上是很多公司月末最常见的对账问题。销售说这个月订单金额是 120 万财务说开票只有 98 万老板在报表里看到的又是 105 万。大家开会争半天最后发现不是谁算错了而是订单状态、开票时间、作废发票、部分开票、客户名称这些细节没有拆清楚。这篇不讲抽象的数据治理。我们就假设有一套最小业务表用 MySQL 先做一组异常明细把“到底哪几笔对不上”查出来。只要异常明细出来了后面无论你用 Excel、BI 看板、Python还是财务系统继续处理方向都会清楚很多。先定一个最小场景假设公司有两张表。一张是销售订单表 sales_orderCREATETABLEsales_order(order_idVARCHAR(50)PRIMARYKEY,customer_nameVARCHAR(100),order_dateDATE,order_amountDECIMAL(12,2),order_statusVARCHAR(20),sales_ownerVARCHAR(50));另一张是开票表 sales_invoiceCREATETABLEsales_invoice(invoice_idVARCHAR(50)PRIMARYKEY,order_idVARCHAR(50),customer_nameVARCHAR(100),invoice_dateDATE,invoice_amountDECIMAL(12,2),invoice_statusVARCHAR(20));这里先不追求完整财务模型只解决一个问题销售订单和开票金额对不上时先查哪些异常。常见状态可以先约定成这样sales_order.order_statusconfirmed、cancelled、draft sales_invoice.invoice_statusnormal、voided正式对账时不要把 draft 订单、cancelled 订单、voided 发票混在一起算否则总数一定容易乱。第一步先算每个订单的有效开票金额开票表里可能有多张发票对应同一个订单也可能有作废发票。第一步先按订单汇总正常发票。WITHinvoice_summaryAS(SELECTorder_id,SUM(CASEWHENinvoice_statusnormalTHENinvoice_amountELSE0END)ASnormal_invoice_amount,SUM(CASEWHENinvoice_statusvoidedTHENinvoice_amountELSE0END)ASvoided_invoice_amount,COUNT(CASEWHENinvoice_statusnormalTHEN1END)ASnormal_invoice_count,COUNT(CASEWHENinvoice_statusvoidedTHEN1END)ASvoided_invoice_countFROMsales_invoiceGROUPBYorder_id)SELECT*FROMinvoice_summary;这一步的目标不是直接出结论而是先把“一个订单多张发票”的情况压成一行。如果你直接拿订单表 JOIN 发票表再 SUM很容易因为一对多关系把订单金额重复放大。第二步查已经确认但还没开票的订单这是最容易被业务关心的一类异常订单已经确认但没有任何正常发票。WITHinvoice_summaryAS(SELECTorder_id,SUM(CASEWHENinvoice_statusnormalTHENinvoice_amountELSE0END)ASnormal_invoice_amountFROMsales_invoiceGROUPBYorder_id)SELECTo.order_id,o.customer_name,o.order_date,o.order_amount,o.sales_owner,COALESCE(i.normal_invoice_amount,0)ASnormal_invoice_amount,o.order_amount-COALESCE(i.normal_invoice_amount,0)ASnot_invoiced_amountFROMsales_order oLEFTJOINinvoice_summary iONo.order_idi.order_idWHEREo.order_statusconfirmedANDCOALESCE(i.normal_invoice_amount,0)0ORDERBYo.order_date;这张表可以命名为confirmed_order_without_invoice。它回答的是哪些确认订单完全没开票。如果这张表里金额很大不要先怪 BI 汇总而要先确认业务上是否允许未开票、是否漏开票、是否订单状态没更新。第三步查部分开票的订单很多对账差异不是完全没开票而是只开了一部分。WITHinvoice_summaryAS(SELECTorder_id,SUM(CASEWHENinvoice_statusnormalTHENinvoice_amountELSE0END)ASnormal_invoice_amountFROMsales_invoiceGROUPBYorder_id)SELECTo.order_id,o.customer_name,o.order_amount,COALESCE(i.normal_invoice_amount,0)ASnormal_invoice_amount,o.order_amount-COALESCE(i.normal_invoice_amount,0)ASdiff_amount,ROUND(COALESCE(i.normal_invoice_amount,0)/NULLIF(o.order_amount,0),4)ASinvoice_rateFROMsales_order oLEFTJOINinvoice_summary iONo.order_idi.order_idWHEREo.order_statusconfirmedANDCOALESCE(i.normal_invoice_amount,0)0ANDABS(o.order_amount-COALESCE(i.normal_invoice_amount,0))0.01ORDERBYABS(o.order_amount-COALESCE(i.normal_invoice_amount,0))DESC;这张表可以命名为partial_invoice_order_detail。它比总金额更有用。总金额只告诉你差了 22 万这张表会告诉你是哪几张订单贡献了这 22 万。第四步查有发票但找不到订单的记录有时问题不是订单没开票而是发票表里的 order_id 填错了或者历史发票没有关联订单。SELECTi.invoice_id,i.order_id,i.customer_nameASinvoice_customer_name,i.invoice_date,i.invoice_amount,i.invoice_statusFROMsales_invoice iLEFTJOINsales_order oONi.order_ido.order_idWHEREi.invoice_statusnormalANDo.order_idISNULLORDERBYi.invoice_date;这张表可以命名为invoice_without_order。如果这里出现很多记录说明不是简单的金额汇总问题而是主键关联规则有问题。后续要统一 order_id 的来源或者增加合同号、客户编码作为辅助关联字段。第五步查客户名称不一致同一个订单号下订单客户名和发票客户名不一致也会让人工核对很痛苦。SELECTo.order_id,o.customer_nameASorder_customer_name,i.customer_nameASinvoice_customer_name,o.order_amount,i.invoice_amount,i.invoice_idFROMsales_order oJOINsales_invoice iONo.order_idi.order_idWHEREi.invoice_statusnormalANDTRIM(o.customer_name)TRIM(i.customer_name)ORDERBYo.order_id;这张表可以命名为customer_name_mismatch。真实项目里这一步经常会发现“有限公司”“有限责任公司”“分公司”“集团简称”之类的问题。如果客户名称是报表维度就不能只靠人工习惯填。最好后面补一张 customer_mapping 表把客户编码、标准客户名、别名统一起来。第六步做一张对账汇总表明细查完以后再做汇总才有意义。WITHinvoice_summaryAS(SELECTorder_id,SUM(CASEWHENinvoice_statusnormalTHENinvoice_amountELSE0END)ASnormal_invoice_amountFROMsales_invoiceGROUPBYorder_id),order_reconcileAS(SELECTo.order_id,o.customer_name,o.sales_owner,o.order_amount,COALESCE(i.normal_invoice_amount,0)ASnormal_invoice_amount,o.order_amount-COALESCE(i.normal_invoice_amount,0)ASdiff_amount,CASEWHENCOALESCE(i.normal_invoice_amount,0)0THEN未开票WHENABS(o.order_amount-COALESCE(i.normal_invoice_amount,0))0.01THEN已匹配WHENCOALESCE(i.normal_invoice_amount,0)o.order_amountTHEN部分开票WHENCOALESCE(i.normal_invoice_amount,0)o.order_amountTHEN超额开票ELSE待确认ENDASreconcile_statusFROMsales_order oLEFTJOINinvoice_summary iONo.order_idi.order_idWHEREo.order_statusconfirmed)SELECTreconcile_status,COUNT(*)ASorder_count,SUM(order_amount)ASorder_amount_total,SUM(normal_invoice_amount)ASinvoice_amount_total,SUM(diff_amount)ASdiff_amount_totalFROMorder_reconcileGROUPBYreconcile_statusORDERBYABS(SUM(diff_amount))DESC;这张表可以命名为sales_invoice_reconcile_summary。它适合放到日报、月报或 BI 看板里但它必须建立在前面的异常明细之上。否则只有汇总没有定位能力。最小交付可以包括 5 张结果表如果我要把这套对账脚本交给业务同事我不会只给一个大 SQL。我会拆成这 5 张结果confirmed_order_without_invoice确认订单未开票 partial_invoice_order_detail部分开票订单 invoice_without_order有发票但找不到订单 customer_name_mismatch客户名称不一致 sales_invoice_reconcile_summary对账状态汇总这样业务、财务、销售看到的不是一句“金额不一致”而是可以分工处理的异常清单。销售看未开票和部分开票。财务看发票状态和作废发票。数据同事看 order_id、客户名、映射规则。管理者看汇总表和差异金额。这类 SQL 最重要的不是写得长而是能解释差异很多人做对账一开始就想写一个万能 SQL把所有情况一次性算完。但真实业务里对账更重要的是解释差异来源。是没开票是部分开票是超额开票是发票没有订单是客户名称不一致是作废发票混进来了只要这些异常类型拆出来后面无论做 Excel 模板、Python 脚本还是 BI 看板都有了落地方向。如果这篇的点赞、收藏或评论合计超过 100我会继续整理一个“销售订单开票对账 SQL 示例包”。这个示例包会包含建表 SQL、测试数据、5 张异常明细查询、汇总查询和一份字段口径说明。你可以直接在 MySQL 里跑一遍再按自己公司的订单表、发票表字段改。