SQLite嵌套SUM语句对非重复记录返回错误值的问题排查

你的核心问题在于嵌套的SUM子查询没有和外层的PROD_CODE关联，导致这个子查询会独立计算所有产品的数量总和，数据库无法匹配到当前行对应的产品，要么返回错误的总和值，甚至可能因返回多行结果报错。另外，外层查询未分组，会导致同一产品的多条重复记录被处理，INSERT OR IGNORE也无法解决总和计算错误的问题。

修正方案一：关联子查询匹配当前产品 #

修改SUM子查询，添加条件关联外层的PROD_CODE，确保只计算当前产品的数量总和：

INSERT OR IGNORE INTO COUNT_REPORT
(
    PRODUCT_CODE, 
    BRANCH, 
    WAREHOUSE, 
    PRODUCT_NAME, 
    CURRENT_COUNT, 
    PREVIOUS_COUNT,
    DIFFERENCE,
    STATE,
    DATE,
    PRICE
)
SELECT
    PROD_CODE,
    (SELECT UNVAN FROM BRANCHES WHERE SUBE_KODU = BRANCH_CODE), 
    (SELECT DEPO_ISMI FROM WAREHOUSES WHERE DEPO_KODU = WARE_CODE),
    (SELECT STOK_ADI FROM PRODUCT_DETAILS WHERE STOK_KODU = PROD_CODE),
    -- 关联外层PROD_CODE，只计算当前产品的总和
    (SELECT SUM(QUANTITY) FROM FINAL_DATA fd_inner WHERE fd_inner.PROD_CODE = fd_outer.PROD_CODE AND fd_inner.TASK_ID = ?),
    PREVIOUS_COUNT,
    DIFFERENCE,
    STATE,
    TASK_DATE,
    PRICE
FROM FINAL_DATA fd_outer
WHERE TASK_ID = ?
-- 分组去重，确保每个产品只插入一条记录
GROUP BY PROD_CODE, BRANCH_CODE, WARE_CODE, PREVIOUS_COUNT, DIFFERENCE, STATE, TASK_DATE, PRICE

修正方案二：外层直接分组计算（更高效） #

直接在外层查询中分组，避免嵌套子查询的性能损耗，同时确保每个产品只统计一次：

INSERT OR IGNORE INTO COUNT_REPORT
(
    PRODUCT_CODE, 
    BRANCH, 
    WAREHOUSE, 
    PRODUCT_NAME, 
    CURRENT_COUNT, 
    PREVIOUS_COUNT,
    DIFFERENCE,
    STATE,
    DATE,
    PRICE
)
SELECT
    fd.PROD_CODE,
    b.UNVAN,
    w.DEPO_ISMI,
    pd.STOK_ADI,
    SUM(fd.QUANTITY) AS CURRENT_COUNT,
    fd.PREVIOUS_COUNT,
    fd.DIFFERENCE,
    fd.STATE,
    fd.TASK_DATE,
    fd.PRICE
FROM FINAL_DATA fd
-- 用JOIN替代嵌套子查询，更清晰高效
LEFT JOIN BRANCHES b ON b.SUBE_KODU = fd.BRANCH_CODE
LEFT JOIN WAREHOUSES w ON w.DEPO_KODU = fd.WARE_CODE
LEFT JOIN PRODUCT_DETAILS pd ON pd.STOK_KODU = fd.PROD_CODE
WHERE fd.TASK_ID = ?
-- 按产品及其他唯一字段分组
GROUP BY fd.PROD_CODE, b.UNVAN, w.DEPO_ISMI, pd.STOK_ADI, fd.PREVIOUS_COUNT, fd.DIFFERENCE, fd.STATE, fd.TASK_DATE, fd.PRICE

关键说明 #

• 两种方案都添加了GROUP BY，确保同一产品只生成一条记录，避免INSERT OR IGNORE忽略有效数据或重复插入。
• 方案二使用JOIN替代嵌套子查询，可读性和性能更优，推荐使用。
• 注意GROUP BY需要包含所有非聚合字段，确保分组逻辑符合你的业务需求（如果同一产品对应不同的分支/仓库，需要保留这些维度的分组）。

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