基于NULL值的SUM多表查询返回结果异常问题

问题场景 #

现有三张表结构：

CREATE TABLE SUPPLIER_INVOICE (
  SUPPLIER_INVOICE_NUMBER VARCHAR(20) PRIMARY KEY,
  FINALISED_STOCK TINYINT(1)
);

CREATE TABLE SUPPLIER_INVOICE_LINE (
  SUPPLIER_INVOICE_LINE_UID BIGINT(20) PRIMARY KEY,
  SUPPLIER_INVOICE_NUMBER VARCHAR(20),
  PURCHASE_ORDER_LINE_UID BIGINT(20),
  PRODUCT_UID BIGINT(20),
  FOREIGN KEY (SUPPLIER_INVOICE_NUMBER) REFERENCES SUPPLIER_INVOICE(SUPPLIER_INVOICE_NUMBER)
);

CREATE TABLE RECEIVED (
  RECEIVED_UID INT(10) PRIMARY KEY,
  ISBN VARCHAR(13),
  SUPPLIER_INVOICE_NUMBER VARCHAR(50),
  RECEIVED_QUANTITY INT(10),
  FOREIGN KEY (SUPPLIER_INVOICE_NUMBER) REFERENCES SUPPLIER_INVOICE(SUPPLIER_INVOICE_NUMBER)
);

执行以下查询得到的结果：

• SELECT * FROM RECEIVED WHERE ISBN = '9781838776329' AND SUPPLIER_INVOICE_NUMBER = 88873733;：返回121条记录，每条RECEIVED_QUANTITY均为1
• SELECT * FROM SUPPLIER_INVOICE WHERE SUPPLIER_INVOICE_NUMBER = 88873733;：返回1条记录，FINALISED_STOCK为0
• SELECT * FROM SUPPLIER_INVOICE_LINE WHERE SUPPLIER_INVOICE_NUMBER = 88873733 AND SUPPLIER_INVOICE_LINE.PRODUCT_UID = 51689207;：返回2条记录，其中1条PURCHASE_ORDER_LINE_UID为NULL
• 关联SUPPLIER_INVOICE、SUPPLIER_INVOICE_LINE和RECEIVED的查询显示：PURCHASE_ORDER_LINE_UID为NULL的发票行对应1条RECEIVED记录（数量1）

但执行以下查询时，预期返回1，实际返回121：

SELECT
    SUM(RECEIVED.RECEIVED_QUANTITY) AS total
FROM 
    RECEIVED
    JOIN SUPPLIER_INVOICE ON RECEIVED.SUPPLIER_INVOICE_NUMBER = SUPPLIER_INVOICE.SUPPLIER_INVOICE_NUMBER
    JOIN SUPPLIER_INVOICE_LINE ON RECEIVED.SUPPLIER_INVOICE_NUMBER = SUPPLIER_INVOICE_LINE.SUPPLIER_INVOICE_NUMBER
WHERE
    SUPPLIER_INVOICE_LINE.PURCHASE_ORDER_LINE_UID IS NULL
    AND SUPPLIER_INVOICE.FINALISED_STOCK = 0
    AND SUPPLIER_INVOICE.SUPPLIER_INVOICE_NUMBER = '88873733'
    AND SUPPLIER_INVOICE_LINE.PRODUCT_UID = 51689207
    AND RECEIVED.ISBN = '9781838776329';

问题原因 #

你的查询仅通过SUPPLIER_INVOICE_NUMBER关联RECEIVED和SUPPLIER_INVOICE_LINE，而RECEIVED中有121条该发票号的记录，SUPPLIER_INVOICE_LINE中有1条符合过滤条件（PURCHASE_ORDER_LINE_UID IS NULL且PRODUCT_UID=51689207）的记录。两者做笛卡尔积后生成121条匹配结果，每条的RECEIVED_QUANTITY都是1，求和后自然得到121，没有区分哪些RECEIVED记录属于这条目标发票行。

解决方案 #

需要添加RECEIVED与SUPPLIER_INVOICE_LINE之间的精准关联条件，确保仅统计目标发票行对应的收货记录。以下提供两种可行方案：

方案1：通过产品关联（若存在产品表） #

如果存在关联PRODUCT_UID和ISBN的产品表（比如PRODUCT表），可以通过该表建立RECEIVED和SUPPLIER_INVOICE_LINE的精准关联：

SELECT SUM(r.RECEIVED_QUANTITY) AS total
FROM RECEIVED r
JOIN SUPPLIER_INVOICE si 
    ON r.SUPPLIER_INVOICE_NUMBER = si.SUPPLIER_INVOICE_NUMBER
JOIN SUPPLIER_INVOICE_LINE sil 
    ON r.SUPPLIER_INVOICE_NUMBER = sil.SUPPLIER_INVOICE_NUMBER
-- 关联产品表，确保收货记录的ISBN对应发票行的产品ID
JOIN PRODUCT p 
    ON sil.PRODUCT_UID = p.PRODUCT_UID 
    AND r.ISBN = p.ISBN
WHERE sil.PURCHASE_ORDER_LINE_UID IS NULL
  AND si.FINALISED_STOCK = 0
  AND si.SUPPLIER_INVOICE_NUMBER = '88873733'
  AND sil.PRODUCT_UID = 51689207
  AND r.ISBN = '9781838776329';

方案2：子查询锁定目标发票行+限定收货记录 #

如果没有产品表，且已知目标发票行仅对应1条RECEIVED记录，可以先通过子查询锁定目标发票行，再结合额外条件（比如已知的RECEIVED_UID）过滤：

SELECT SUM(r.RECEIVED_QUANTITY) AS total
FROM RECEIVED r
JOIN SUPPLIER_INVOICE si 
    ON r.SUPPLIER_INVOICE_NUMBER = si.SUPPLIER_INVOICE_NUMBER
JOIN (
    -- 子查询获取目标发票行
    SELECT SUPPLIER_INVOICE_NUMBER
    FROM SUPPLIER_INVOICE_LINE
    WHERE SUPPLIER_INVOICE_NUMBER = '88873733'
      AND PRODUCT_UID = 51689207
      AND PURCHASE_ORDER_LINE_UID IS NULL
) sil 
    ON r.SUPPLIER_INVOICE_NUMBER = sil.SUPPLIER_INVOICE_NUMBER
WHERE si.FINALISED_STOCK = 0
  AND r.ISBN = '9781838776329'
  -- 添加目标收货记录的唯一标识（根据之前的查询结果补充）
  AND r.RECEIVED_UID = [对应收货记录的ID];

内容的提问来源于stack exchange，提问作者Luis Fernandes