假设订单表里有四笔订单,金额合计三百元。为了在报表里增加支付信息,把订单与支付流水做一次 LEFT JOIN,再汇总订单金额,结果却变成四百元。没有新增订单,连接条件也写了订单编号,金额为什么会增加?

关键在于:保留左表的记录,不等于让左表的每条记录只出现一次。 一笔订单可以对应多条支付流水;连接后的每一行,已经不能继续当成一笔独立订单。金额列看起来没变,它被累计的次数却变了。

下面用一个完整的小实验定位这个问题。目标是同时得到所有订单的总额与已结算支付总额;两者是不同指标,不要求相等。数据全部为教学构造,金额使用整数“分”,不涉及真实交易。文中的查询与反例均在 SQLite 实际运行,完整脚本放在文末。

先确定:一行究竟代表什么

实验有 orders、payments 两张主要表。订单编号与支付编号各自是主键;支付表的 order_id 指向订单,但没有唯一约束。settled 表示本例纳入统计的已结算流水,pending 表示不纳入的待处理流水。

订单编号订单金额/分支付流水
11000011:已结算 6000;12:已结算 4000
21000021:待处理 10000
3600031:已结算 6000
44000没有流水

订单表的一行代表一笔订单,支付表的一行代表一条流水。实验故意让订单 1 和订单 2 金额相等,也故意区分“完全没有流水”和“有流水但不符合条件”。这两组差异,能检验后面看似有效的修补是否真的成立。

单独统计订单,得到四笔、合计 30000 分;筛选已结算支付,得到三条、合计 16000 分。这是核对连接结果的两份基准。若先写出一条很长的汇总查询,再只检查最后一个数字,就不容易发现究竟是哪一层改变了记录的含义。

外键也不会替我们限制支付条数。这里启用了外键检查,同一订单的两条支付仍然合法:存在性约束并没有要求子表中的订单编号只能出现一次。SQLite 官方外键文档也将它解释为表间的存在关系。SQLite:外键约束

去掉 SUM,先看连接出来的五行

先不要汇总,只展开订单与符合条件的流水:

SELECT o.id AS order_id, o.total_cents,
       p.id AS payment_id, p.amount_cents
FROM orders AS o
LEFT JOIN payments AS p
  ON p.order_id = o.id AND p.status = 'settled'
ORDER BY o.id, p.id;
order_idtotal_centspayment_idamount_cents
110000116000
110000124000
210000NULLNULL
36000316000
44000NULLNULL

订单 1 匹配到两条支付,所以出现两次。订单 2 的待处理支付不满足 ON 中的条件,订单 4 没有支付;它们各保留一行,右侧补为 NULL。这正是左外连接的规则。SQLite:FROM 与 LEFT JOIN

此时累计 total_cents,实际做的是 10000 + 10000 + 10000 + 6000 + 4000,结果自然是 40000。数据库没有修改订单金额;查询把订单 1 的金额放进求和输入两次。

对本例这种按订单编号匹配的连接,一笔订单若有若干条符合条件的流水,就输出相应条数;没有匹配则输出一条补空行。这是结果的逻辑含义,不能据此断言数据库执行时一定先生成完整笛卡尔积,也不能拿它当执行计划或性能测量。

COUNT、DISTINCT、GROUP BY 为什么不能随手替换

同一个连接结果,可以产生几种都“算对了”、却回答不同问题的统计:

表达式实测结果它在统计什么
COUNT(*)5连接结果的行数
COUNT(p.id)3非空的支付编号数量
COUNT(DISTINCT o.id)4不同的订单编号数量
SUM(o.total_cents)40000每个连接结果行携带的订单金额之和
SUM(DISTINCT o.total_cents)20000不同金额值之和

最后一行尤其容易误用。它保留的是 10000、6000、4000 这三个不同的数值,因此把两笔同价订单也合并了。统计对象是订单身份时,金额相等不能成为删除一笔订单的理由。SQLite 对聚合参数 DISTINCT、COUNT(*) 与 COUNT(X) 的定义可以直接核对。SQLite:聚合函数

COUNT(p.id) 在这里适合数支付,是因为支付主键不会为空,而补空行中的它为空。若改数一个业务上允许为空的备注字段,有支付但没备注的流水也会被漏掉。按订单分组后,订单 4 的 COUNT(*) 是一,COUNT(p.id) 才是零;这个差异不能靠改个列名消除。

同样,给原查询补上 GROUP BY o.id,再在组内 SUM(o.total_cents),订单 1 仍会得到 20000。分组只是把重复行放进同一个组,没有自动撤销重复累加。若只需要订单字段,可以明确按订单身份去重;若还需要支付汇总,更清楚的办法是先把支付整理到订单这一层。

需要所有订单及支付汇总:先聚合右表

让支付子查询按订单编号分组,得到“每个订单至多一行”的结果,再连接订单表:

WITH paid AS (
  SELECT order_id, COUNT(*) AS payment_count,
         SUM(amount_cents) AS paid_cents
  FROM payments
  WHERE status = 'settled'
  GROUP BY order_id
)
SELECT o.id AS order_id, o.total_cents,
       COALESCE(paid.payment_count, 0) AS payment_count,
       COALESCE(paid.paid_cents, 0) AS paid_cents
FROM orders AS o
LEFT JOIN paid ON paid.order_id = o.id
ORDER BY o.id;

实际返回的四行,按“订单编号、订单金额、支付条数、支付合计”排列,分别是 (1, 10000, 2, 10000)、(2, 10000, 0, 0)、(3, 6000, 1, 6000)、(4, 4000, 0, 0)。再累计这份结果,订单总额为 30000 分,支付总额为 16000 分。

这里重要的是连接键的唯一性。子查询的分组保证每个订单编号最多一行,因而每条订单记录不会被这一侧展开。代码里的 WITH 只是让这个边界容易读,并不意味着它必定生成某种物理临时表,或在任何数据量上都更快。本文没有做性能比较。

要证明连接不增加订单行数,需要证明右侧对当前连接键至多一条匹配;“目前测试数据恰好只有一条”只是这批数据的观察。如果业务允许分次支付,不能为了报表好算,给支付表强加每单唯一。应该保留合法明细,在查询中明确要哪一层的汇总。

COALESCE 也有业务前提:本例规定“没有符合条件的支付”在该指标里按零记,而支付金额列本身不允许为空。若空值表示金额未知,直接补零会改变含义,不能照搬。在所有订单都为空的边界实验中,原始 SUM 返回 NULL;附件的总计查询明确用外层 COALESCE 将空报表显示为零,这是显示口径的选择。

如果报表只想要订单总额,直接从订单表统计就足够了。增加一次连接应当服务于确实需要的维度或指标。先写清输出的一行代表一笔订单,才能判断引入的子查询是否还维持这个约定。

ON 与 WHERE,决定没有合格支付的订单是否留下

把支付状态条件从 ON 移到 WHERE,会得到另一份结果:

SELECT o.id AS order_id, p.id AS payment_id
FROM orders AS o
LEFT JOIN payments AS p ON p.order_id = o.id
WHERE p.status = 'settled'
ORDER BY o.id, p.id;

本例只剩订单 1 的两条流水和订单 3 的一条。订单 2 的待处理行不满足条件,订单 4 的补空行也不能使条件为真,因此都被筛掉。PostgreSQL 官方文档同样用并排示例说明外连接中 ON 与 WHERE 的差别;本文实际执行的数据库仍是 SQLite。PostgreSQL:外连接条件与后续过滤

再补一句 OR p.id IS NULL,也不能完整恢复最初目标。实测能找回订单 4,却找不回订单 2:后者已经匹配过待处理流水,并没有得到支付编号为空的补空行。“没有匹配到任何流水”和“没有符合状态条件的流水”,不是同一个集合。

若目标本来就是只看至少有一条已结算支付的订单,又不需要展示支付明细,可以直接表达存在性:

SELECT o.id AS order_id, o.total_cents
FROM orders AS o
WHERE EXISTS (
  SELECT 1 FROM payments AS p
  WHERE p.order_id = o.id AND p.status = 'settled'
)
ORDER BY o.id;

实测只返回订单 1 与订单 3,两笔订单合计 16000 分,订单 1 不会因为有两条支付就出现两次。这里恰好与支付合计相等;若还有未付清的部分,两种金额就未必相同。EXISTS 判断的是子查询有没有行。SQLite:EXISTS 它没有回答支付有几条、支付多少钱,也不代表这个写法在所有场景中最快。

同时连接两张明细表,重复会互相放大

接着给订单 1 增加三条商品明细,编号为 101、102、103。如果支付和商品明细都只通过订单编号连接,同一订单的每条支付就会与每条商品明细配对。两条支付乘三条商品明细,共六行。

附件实际列出了六组支付与商品编号:支付 11 对应三条商品,支付 12 也对应同样三条商品。在只筛订单 1 的这份结果里,订单金额合计变成 60000 分,支付金额合计也从 10000 变成 30000 分。重复已经不只影响父表金额。

若输出仍是一笔订单一行,应分别把支付汇总成每单一行,把商品整理成每单一行,再一起连接。实验按这个方式得到 (1, 10000, 10000, 3),依次表示订单编号、订单金额、支付合计和商品条数。只预聚合支付、再直接展开商品,仍会重复订单金额和已聚合的支付金额。

不过,如果业务确实有“哪条支付对应哪条商品”的分配表,就应该按真实的关联键与分配金额计算。本文构造的数据没有这层关系,六种配对不能被当成六次实际分配。SQL 可以合法地产生结果,不代表数据模型已经表达了报表想问的问题。

怎样复现,再把检查带回自己的报表

实验日期为 2026 年 10 月 18 日,环境为 CPython 3.12.14、SQLite 3.53.1、macOS 27.0.1 arm64。脚本使用 Python 标准库,在内存中新建数据库,显式启用外键检查,执行十三条命名查询,并校验预期结果;不会连接业务数据库。

下载完整实验包并解压后运行:

python3 sql-join-cardinality.py > reproduced.json

脚本按 Python 3.9 及以上语法编写,本次实际运行的是上述 3.12.14 环境。也可以单独查看脚本、本次结果 JSON与复现说明。结果保留建表语句、全部查询和环境信息,另一台机器重跑时环境字段可能不同。

除正文反例外,还实际运行了三个边界:删掉订单 1 的第二条已结算支付后,每单至多一个匹配,总额不再膨胀;删除全部支付后,四笔订单仍保留,支付计数为零;清空全部订单后,检验原始空集合与明确补零的总计结果。它们分别检查重复、无匹配和空报表,没有验证其他数据库、并发更新或大数据量性能。

排查自己的报表时,可以先独立算出主表基准,再去掉聚合,保留两侧主键查看展开结果。随后按主表主键统计每组匹配条数,检查筛选条件究竟是在选择匹配,还是删除整个输出行。最后才决定要明细、每个主体的汇总,还是仅判断有没有相关记录。

所以,开头的四百元来自一对多连接中的重复累加。可靠的修正,是让求和输入的一行与指标的统计单位一致,并让后续每次连接都维持这个约定。先确认一行代表什么,再决定这一列能不能相加。