一张客户报表要列出所有活跃客户,再带上他们的已付款订单。没有已付款订单的人也应出现,订单数写成零。查询写了 LEFT JOIN,却只剩一个客户。检查这类问题时,可以先看状态条件放在哪里。写在 ON 里,它决定哪笔订单能接到客户那一行。写在 WHERE 里,它决定连接产生的那一行能否进入报表。这两个位置会让没有符合条件订单的客户得到不同的结果。

实验用五名客户。客户 1 有两笔已付款订单 101 和 102。客户 2 没有订单,客户 3 只有待付款订单 103,客户 4 有订单 104,但该笔订单的状态存为 NULL。这四名客户都活跃。客户 5 已停用,即使有一笔已付款订单 105,也应排除。分别保留这几种情况,比只造一个有订单和一个没订单的客户更容易发现过滤条件出了什么问题。

SELECT c.id, o.id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
WHERE c.active = 1
ORDER BY c.id, o.id

可以按结果的逻辑来读这段查询。先对每位客户找所有满足 ON 的订单。客户 1 有两条匹配,所以得到两行。客户 2、3、4 都没有匹配,左连接各补一行,订单一侧的列全是 NULL。对客户 3 来说,那一行不是把订单 103 的状态清空。订单 103 没通过匹配,出现的空行是左连接为客户补出来的。最后的 WHERE c.active = 1 检查客户列,剔除客户 5,保留前面四位。SQLite 文档明确把补空行放在 ON 之后、WHERE 之前。这是理解结果的顺序,实际执行可以由优化器另作安排。SQLite 的 SELECT 处理规则

把付款条件移到 WHERE 后,ON 只按客户编号找订单。客户 2 仍会先得到补的空行。客户 3、4 则各自接上真实订单。检查付款状态时,客户 3 的比较结果为假。客户 2 的补空行和客户 4 的真实订单都得到未知值 NULL。在 WHERE 中,只有条件为真的行能留下。这三个客户都会消失。连接先前为无匹配项补过行,后面的过滤仍然可以把它删掉。

查询写法实际得到的客户和订单编号
付款状态放在 ON(1, 101), (1, 102), (2, NULL), (3, NULL), (4, NULL)
付款状态移到 WHERE(1, 101), (1, 102)
在 WHERE 中加 OR o.id IS NULL(1, 101), (1, 102), (2, NULL)
把客户是否活跃也放进 ON(1, 101), (1, 102), (2, NULL), (3, NULL), (4, NULL), (5, NULL)

第三行是一个常见的修补思路。希望付款条件通过时保留订单,再额外放过订单编号为空的行。它确实找回了完全没有订单的客户 2。可是客户 3 和 4 早在只按客户编号的连接阶段匹配到了真实订单,数据库没有为他们再补空行。订单编号存在,付款条件也通不过,这两人还是不见了。要复核这类条件,最好同时比较“从来没有子记录”和“有子记录但不符合筛选”,这两类数据可以把看似相同的写法区分开。

表的最后一行又说明,左表自己的筛选也有放错位置的时候。如果把 c.active = 1 从 WHERE 移到 ON,客户 5 只是无法接上已付款订单。作为左表的一员,他仍被保留,右侧补空。客户是否属于报表的范围,适合放在 WHERE。哪些订单属于某个客户的统计范围,适合写成这个左连接的匹配条件。如果需求改成只列有已付款订单的客户,可以直接写内连接,或者按客户判断付款订单是否存在。这样看到查询的人能知道报表范围本身已经改变。

可以把同一个判断用在日期条件上。假设报表列出本月所有活跃客户,即使某人只在上月下过单,也应留在名单里。本月的时间范围就应当和付款状态一起决定订单是否匹配。有订单的历史记录永远存在,不代表当月有统计对象。若另有要求是只显示本月新注册的客户,那是左表自己的条件,放在筛选客户的位置。先把“是否列出客户”和“是否纳入这笔订单”写成两个问题,可以看清筛选条件归属。

连接能保留空行以后,统计也要用对计数口径。以客户分组,COUNT(*) 会数每一行,包括左连接补出来的一行。客户 2、3、4 在实验中的 COUNT(*) 都是 1。实际的订单数要用 COUNT(o.id)。真实订单编号由主键提供,不会为空,但补空行的订单编号为空,于是这三位的订单数是 0。SQLite 的聚合函数说明

SELECT c.id, COUNT(*), COUNT(o.id), COUNT(o.note)
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
WHERE c.active = 1
GROUP BY c.id
ORDER BY c.id

客户 1 的三项计数是 2、2、1。两笔订单只有一笔有备注,所以可空列 o.note 只能回答有几笔订单填写了备注。用它数订单,会把真实但缺少备注的那一笔遗漏。也不能指望用 COALESCE 纠正已经数错的行。错误的 COUNT(*) 给出的是实际数到的 1,替换空值对它没有影响。若把订单表清空再运行这段查询,四名活跃客户仍然出现,每位都有一条补空行,COUNT(o.id) 全部为 0。这是核对报表是否能在空数据下照常显示客户的一种直接办法。

如果报表还要在一行上同时显示全部、已付款、待付款和状态缺失的订单数,就可以先按客户编号连接所有订单,再给不同的聚合各自加 FILTER。它只改变参与那个聚合的行,其他列的计数仍可使用同一个分组。脚本里客户 3 有 1 笔订单,进入待付款一栏。客户 4 也有 1 笔,进入状态缺失一栏。统计缺失状态时仍用 COUNT(o.id),所以客户 2 虽然也有一行状态为空,状态缺失的订单数仍为 0。这个写法保留了报表需要比较的各类订单,代价是右侧匹配范围现在包含所有订单。SQLite 的 FILTER 语义

有时需要的只有名单,例如查“没有已付款订单的人”。可以在原来的付款匹配上加 WHERE 条件 o.id IS NULL。实验得到客户 2、3、4。如果改成连接所有订单再检查空编号,则只得到客户 2,回答的是谁从来没有订单。检查 o.status IS NULL 又不同。真实订单 104 的状态就是空值,客户 4 的那一行与客户 2 的补空行都会被选出。用某列判断连接是否缺失,需要先知道真实匹配行能否在该列存空值。

同一份名单还可以用 NOT EXISTS。子查询只负责查当前客户有没有符合付款条件的订单。外层每位活跃客户本来就只有一行,右表匹配多少条都不会扩大名单。实验得到的客户仍是 2、3、4。这里的 SELECT 1 只是表达只关心有没有返回行,子查询即使选了一个空值列也不改变存在性判断。SQLite 的 EXISTS 运算符

SELECT c.id
FROM customers AS c
WHERE c.active = 1
  AND NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id AND o.status = 'paid'
  )
ORDER BY c.id

两种查无匹配的写法也可以相互校验。如果查询本来要查的是“有订单,但没有已付款订单”,就还需要判断至少有一笔订单,否则客户 2 会混进名单。实验中客户 3 和 4 都符合这个更窄的范围,但他们的原因不同。应用是否要把未知状态和待付款分开,需要回到报表定义。用否定条件时,尤其要留意“找不到满足条件的行”和“每一行都明确满足相反条件”之间的差别。存在空状态时,后者通常需要额外约定。

即使过滤和空值都处理正确,汇总金额还会遇到一对多关系。客户 1 的两笔订单金额是 50 和 70。实验给订单 101 加两条退款记录,金额为 4 和 6,给订单 102 加一条退款记录,金额为 7。直接连接退款明细后,订单 101 被展开成两行。这样数订单编号得到 3,订单金额加成 170,而退款总额仍是 17。一个数字碰巧正确,不能证明同组里的其他聚合也采用了同一粒度。

这时 COUNT(DISTINCT o.id) 能把订单数恢复为 2,订单金额仍需单独处理。用 SUM(DISTINCT o.amount) 会按金额去重,两笔金额相同的不同订单也会被合成一笔。脚本把客户 1 的两笔订单金额都设成 50,再单独运行两个求和。普通求和是 100,按金额去重只得 50。脚本先按订单编号汇总退款,再把每笔订单的退款小计接回订单。此后每笔匹配订单最多出现一次,最后按客户聚合,客户 1 的结果是 2 笔订单、120 的订单金额和 17 的退款金额。其余活跃客户仍有结果行,金额和计数都显示 0。示例中订单和退款金额必填,因此把空聚合显示成 0 有明确含义。如果金额允许缺失,要先确定缺数据是否与确实为零应显示成同一个结果。

WITH refund_by_order AS (
  SELECT order_id, SUM(amount) AS refund_total
  FROM refunds
  GROUP BY order_id
)
SELECT c.id, COUNT(o.id),
       COALESCE(SUM(o.amount), 0),
       COALESCE(SUM(r.refund_total), 0)
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
LEFT JOIN refund_by_order AS r ON r.order_id = o.id
WHERE c.active = 1
GROUP BY c.id
ORDER BY c.id

实际修改报表时,可以先查看去掉聚合的几行,写出每行对应哪个客户、订单和退款。若原来要一笔订单一行,在连接某张表后同一编号出现多次,就先检查那张表能否在订单粒度汇总。也要确认另一项金额的来源。如果退款和发货都各有多条明细,直接连两张表会重复组合,甚至原先算对的退款也会被另一边放大。分别汇总到订单粒度,能在最终按客户求和之前保住各项金额的含义。

排查时还可以对照查询计划。只要条件要求右侧某列必须有值才能通过,SQLite 有时会把外连接简化成内连接。看到执行计划的连接方式变化时,先查输出范围是否符合预期。优化器可以按等价规则减少工作,决定哪些客户显示在报表里的,仍是查询本身的过滤语义。这里用结果集合做验证,比要求执行计划一定包含某个字样更直接。SQLite 的外连接简化规则

以上查询在 2026 年 11 月 16 日用 macOS 27.0.1、arm64、Python 3.9.6 和 SQLite 3.54.0 实际运行。脚本在内存里建表并比对每条查询的结果,还单独清空订单表核对零计数。它可以复现行是否出现、如何计数及连接如何扩大金额统计。这个小样本用于验证结果的含义,执行计划与耗时需要用目标数据库和实际数据另行检查。

python3 left-join-filter-lab.py > report.json

下载完整脚本 · 查看运行结果