LEFT JOIN 过滤与计数

一份报表要展示所有客户及其已完成工单数量。使用了 LEFT JOIN，结果没有工单的客户消失了；把过滤条件移到 ON 后，客户回来了，没有工单的客户却又被统计为 1。

这两个问题有不同的原因：右表条件放在 WHERE 中，可能过滤掉外连接补出的 NULL 行；COUNT(*) 则会把这条补出的行计入总数。针对这个需求，应把工单状态条件放在 ON 中，并统计右表非空主键 COUNT(t.TICKET_ID)。

下面以 DM8 为例，用一组虚构的客户和工单数据演示。

1. 先定义报表中的一行代表什么

假设需求是：每个客户一行，展示已完成工单数；没有工单、只有未完成工单的客户也必须展示，数量为 0。

测试数据包含三种边界：一个客户有多张已完成工单，一个客户只有未完成工单，一个客户完全没有工单。

客户编号	工单情况	应显示的已完成数量
1	两张 DONE、一张 OPEN	2
2	一张 OPEN	0
3	没有工单	0

CREATE TABLE BLOG_CUSTOMER_0917 (
    CUSTOMER_ID INT PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(20)
);

CREATE TABLE BLOG_TICKET_0917 (
    TICKET_ID INT PRIMARY KEY,
    CUSTOMER_ID INT NOT NULL,
    STATUS VARCHAR(10) NOT NULL
);

INSERT INTO BLOG_CUSTOMER_0917 VALUES (1, 'Alice');
INSERT INTO BLOG_CUSTOMER_0917 VALUES (2, 'Bob');
INSERT INTO BLOG_CUSTOMER_0917 VALUES (3, 'Carol');

INSERT INTO BLOG_TICKET_0917 VALUES (101, 1, 'DONE');
INSERT INTO BLOG_TICKET_0917 VALUES (102, 1, 'DONE');
INSERT INTO BLOG_TICKET_0917 VALUES (103, 1, 'OPEN');
INSERT INTO BLOG_TICKET_0917 VALUES (201, 2, 'OPEN');
COMMIT;

2. 第一次错误来自 WHERE 的过滤位置

下面的 SQL 看起来合理：先关联工单，再筛选已完成状态。

SELECT c.CUSTOMER_ID, t.TICKET_ID, t.STATUS
FROM BLOG_CUSTOMER_0917 c
LEFT JOIN BLOG_TICKET_0917 t
  ON t.CUSTOMER_ID = c.CUSTOMER_ID
WHERE t.STATUS = 'DONE'
ORDER BY c.CUSTOMER_ID, t.TICKET_ID;

预期结果只剩两行：客户 1 的工单 101、102。客户 2 关联到的状态是 OPEN，条件不成立；客户 3 没有匹配工单，右表列补为 NULL，NULL = 'DONE' 也不为真，因此同样被过滤。

对左外连接的说明是保留左表记录，并以 NULL 填充未匹配的右表字段。这个保留发生在外连接的结果语义中，后续查询条件仍然可以筛掉这些行。

3. 把工单资格写进 ON

SELECT c.CUSTOMER_ID, t.TICKET_ID, t.STATUS
FROM BLOG_CUSTOMER_0917 c
LEFT JOIN BLOG_TICKET_0917 t
  ON t.CUSTOMER_ID = c.CUSTOMER_ID
 AND t.STATUS = 'DONE'
ORDER BY c.CUSTOMER_ID, t.TICKET_ID;

此时的意思是：为每个客户匹配“属于该客户且状态为 DONE”的工单；如果没有合格工单，就保留客户并补 NULL。

注意，保留所有客户并不代表只返回三行。客户 1 有两张合格工单，就会产生两行匹配结果。普通连接会保留匹配组合，这也说明不能把“内连接是交集”的教学类比直接当成自动去重规则。

还有一种写法：仍按客户编号连接，然后在 WHERE 中写 t.STATUS = 'DONE' OR t.TICKET_ID IS NULL。它能保留没有任何工单的客户 3，却仍然丢掉只有 OPEN 工单的客户 2。因为客户 2 已经匹配到了实际工单，不会额外生成一条 NULL 行。

4. 第二次错误来自 COUNT 的计数对象

将正确的外连接结果直接分组，但使用 COUNT(*)，依旧会得到错误的业务数量：

SELECT c.CUSTOMER_ID,
       COUNT(*) AS JOIN_ROW_COUNT,
       COUNT(t.TICKET_ID) AS DONE_TICKET_COUNT
FROM BLOG_CUSTOMER_0917 c
LEFT JOIN BLOG_TICKET_0917 t
  ON t.CUSTOMER_ID = c.CUSTOMER_ID
 AND t.STATUS = 'DONE'
GROUP BY c.CUSTOMER_ID
ORDER BY c.CUSTOMER_ID;

COUNT(*) 统计分组内的行数，包括外连接补出的行；COUNT(t.TICKET_ID) 只统计该表达式非 NULL 的行。由于工单编号是主键，真实工单的编号非空，未匹配的占位行编号为 NULL，因此它适合表达本例的工单数量。

最终查询可以写成：

SELECT c.CUSTOMER_ID, c.CUSTOMER_NAME,
       COUNT(t.TICKET_ID) AS DONE_TICKET_COUNT
FROM BLOG_CUSTOMER_0917 c
LEFT JOIN BLOG_TICKET_0917 t
  ON t.CUSTOMER_ID = c.CUSTOMER_ID
 AND t.STATUS = 'DONE'
GROUP BY c.CUSTOMER_ID, c.CUSTOMER_NAME
ORDER BY c.CUSTOMER_ID;

不能随意把计数列换成一个可空的备注字段，否则真实存在但备注为空的工单也会被漏计。另外，COUNT(t.TICKET_ID) 解决的是占位行问题；如果后续又关联了多条附件或处理记录，同一工单可能重复出现，计数仍可能膨胀。此时应重新确认关联后的数据粒度，评估先聚合明细再关联的方式，而不是认为换了 COUNT 就能解决所有重复问题。

5. 只判断有无时，用 EXISTS 表达需求

如果业务改成“列出有已完成工单的客户”，就不需要返回或统计工单明细。可以直接写：

SELECT c.CUSTOMER_ID, c.CUSTOMER_NAME
FROM BLOG_CUSTOMER_0917 c
WHERE EXISTS (
    SELECT 1
    FROM BLOG_TICKET_0917 t
    WHERE t.CUSTOMER_ID = c.CUSTOMER_ID
      AND t.STATUS = 'DONE'
)
ORDER BY c.CUSTOMER_ID;

本例预期只返回客户 1 一次。EXISTS 判断子查询结果是否至少包含一行。因此外表的一条客户记录不会因为有两张匹配工单而重复输出。

反过来，查询“没有已完成工单的客户”，可使用 NOT EXISTS，本例应返回客户 2、3：

SELECT c.CUSTOMER_ID, c.CUSTOMER_NAME
FROM BLOG_CUSTOMER_0917 c
WHERE NOT EXISTS (
    SELECT 1
    FROM BLOG_TICKET_0917 t
    WHERE t.CUSTOMER_ID = c.CUSTOMER_ID
      AND t.STATUS = 'DONE'
)
ORDER BY c.CUSTOMER_ID;

6. 正确性验证

先运行小数据测试，检查客户集合、每个客户的重复次数和计数是否分别符合预期：错误 WHERE 写法只有客户 1；正确外连接明细有四行；最终分组查询有三行，数量为 2、0、0；EXISTS 返回客户 1；NOT EXISTS 返回客户 2、3。

再测试让一个客户有更多工单，加入可空业务字段，以及需要关联的附件或处理记录，验证报表仍然保持“一行一个客户”。如果真实工单状态允许 NULL，也应单独覆盖该情况。

结果核对正确后，再用接近业务规模的数据比较执行计划。候选索引可以结合客户关联列与状态过滤列评估，但列顺序和是否值得建立，需要看选择性、其他查询及维护成本，不能从四条工单的实验得出结论。记录实际耗时和访问量，不以计划是否出现某个连接名称作为唯一优化标准。

编写关联报表时，先问“最终一行代表什么”，再决定匹配条件、过滤位置和计数对象。客户保留范围由连接及后续过滤共同决定，工单数量则取决于聚合输入中的真实明细。把这两件事分别验证，比看到结果异常就加 DISTINCT 更容易找到原因。

连接和计数的具体用法，可查阅《DM8_SQL语言使用手册》的外连接、集函数与 EXISTS 章节。