求含LINE_MARK=-1的PRODUCT_ID的条件SUM值的SQL实现

表结构 #

CREATE TABLE `tbl_erp_invoice_lines` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `INVOICE_ID` INT(11) NULL DEFAULT NULL,
    `PRODUCT_ID` INT(11) NULL DEFAULT NULL,
    `LINE_UNIT` VARCHAR(10) NULL DEFAULT 'KG' COLLATE 'utf8_bin',
    `LINE_PRICE` DECIMAL(7,3) NULL DEFAULT NULL,
    `LINE_QUANTITY` INT(11) NULL DEFAULT NULL,
    `LINE_VAT` INT(11) NULL DEFAULT '18',
    `LINE_MARK` INT(11) NULL DEFAULT NULL,
    `CREATEDON` DATETIME NULL DEFAULT current_timestamp(),
    `CREATEDBY` INT(11) NULL DEFAULT '1',
    `LASTUPDATEON` DATETIME NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    `LASTUPDATEBY` INT(11) NULL DEFAULT '1',
)

字段约束与需求 #

• LINE_MARK的取值仅为-1或1
• 同一PRODUCT_ID可能对应多条记录，其LINE_MARK可为-1或1
• 需求：对至少存在一条LINE_MARK=-1记录的PRODUCT_ID，计算所有对应记录的SUM(LINE_PRICE*LINE_QUANTITY*LINE_MARK)；若某个PRODUCT_ID没有LINE_MARK=-1的记录，则排除该PRODUCT_ID的所有记录。

现有SQL的问题 #

现有语句仅筛选LINE_MARK=-1的记录求和，会漏掉对应PRODUCT_ID下LINE_MARK=1的记录，不符合需求：

select SUM(LINE_PRICE*LINE_QUANTITY*LINE_MARK) from tbl_erp_invoice_lines 
where LINE_MARK=-1

正确SQL写法 #

方式一：子查询筛选符合条件的PRODUCT_ID

SELECT SUM(LINE_PRICE * LINE_QUANTITY * LINE_MARK)
FROM tbl_erp_invoice_lines
WHERE PRODUCT_ID IN (
    SELECT DISTINCT PRODUCT_ID
    FROM tbl_erp_invoice_lines
    WHERE LINE_MARK = -1
)

方式二：JOIN关联筛选（大数据量场景更高效）

SELECT SUM(t1.LINE_PRICE * t1.LINE_QUANTITY * t1.LINE_MARK)
FROM tbl_erp_invoice_lines t1
JOIN (
    SELECT DISTINCT PRODUCT_ID
    FROM tbl_erp_invoice_lines
    WHERE LINE_MARK = -1
) t2 ON t1.PRODUCT_ID = t2.PRODUCT_ID

按PRODUCT_ID单独输出结果（可选）

如果需要每个符合条件的PRODUCT_ID单独显示求和值，添加GROUP BY即可：

SELECT PRODUCT_ID, SUM(LINE_PRICE * LINE_QUANTITY * LINE_MARK) AS total
FROM tbl_erp_invoice_lines
WHERE PRODUCT_ID IN (
    SELECT DISTINCT PRODUCT_ID
    FROM tbl_erp_invoice_lines
    WHERE LINE_MARK = -1
)
GROUP BY PRODUCT_ID

逻辑说明 #

两种写法的核心都是先筛选出所有存在LINE_MARK=-1的PRODUCT_ID，再基于这些PRODUCT_ID汇总所有对应记录的计算值，不会遗漏LINE_MARK=1的相关记录。

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