MySQL查询如何添加WHERE条件筛选sell_qty与sold_qty不匹配的数据

调整方案 #

原语句无法直接在WHERE子句中使用sell_qty、sold_qty别名筛选，因为SQL执行顺序中WHERE阶段早于SELECT别名定义阶段，你可以用以下两种方式调整：

方案1：直接在现有语句基础上加HAVING子句 #

在原语句的GROUP BY之后新增HAVING筛选即可，调整后的完整语句如下：

SELECT 
purchase_lines.`product_id`,
(SELECT SUM(`quantity`) FROM `purchase_lines` 
WHERE transaction_sell_lines.product_id = purchase_lines.product_id GROUP BY product_id LIMIT 1 ) as purchase_qty,
(SELECT SUM(`quantity`) FROM `transaction_sell_lines` 
WHERE transaction_sell_lines.product_id = purchase_lines.product_id GROUP BY product_id LIMIT 1 ) as sell_qty,
(SELECT SUM(`quantity_sold`) FROM `purchase_lines` 
WHERE transaction_sell_lines.product_id = purchase_lines.product_id GROUP BY product_id LIMIT 1 ) as sold_qty,
variation_location_details.qty_available as avaiable_qty
FROM 
`purchase_lines`, 
variation_location_details,
transaction_sell_lines
WHERE 
purchase_lines.product_id = variation_location_details.product_id
AND
variation_location_details.product_id = transaction_sell_lines.product_id
GROUP BY 
variation_location_details.product_id
-- 新增HAVING筛选不匹配的记录
HAVING sell_qty <> sold_qty

注意：如果两个字段可能存在NULL值，建议把判断条件改为HAVING NOT (sell_qty <=> sold_qty)，避免NULL值比对时结果为NULL被遗漏。

方案2：套一层子查询后在WHERE筛选（可读性更高） #

如果不习惯用HAVING，也可以把原有查询作为子查询，在外层加WHERE条件：

SELECT * FROM (
    SELECT 
    purchase_lines.`product_id`,
    (SELECT SUM(`quantity`) FROM `purchase_lines` 
    WHERE transaction_sell_lines.product_id = purchase_lines.product_id GROUP BY product_id LIMIT 1 ) as purchase_qty,
    (SELECT SUM(`quantity`) FROM `transaction_sell_lines` 
    WHERE transaction_sell_lines.product_id = purchase_lines.product_id GROUP BY product_id LIMIT 1 ) as sell_qty,
    (SELECT SUM(`quantity_sold`) FROM `purchase_lines` 
    WHERE transaction_sell_lines.product_id = purchase_lines.product_id GROUP BY product_id LIMIT 1 ) as sold_qty,
    variation_location_details.qty_available as avaiable_qty
    FROM 
    `purchase_lines`, 
    variation_location_details,
    transaction_sell_lines
    WHERE 
    purchase_lines.product_id = variation_location_details.product_id
    AND
    variation_location_details.product_id = transaction_sell_lines.product_id
    GROUP BY 
    variation_location_details.product_id
) t
WHERE t.sell_qty <> t.sold_qty

额外优化建议 #

原SQL用的是隐式交叉连接关联表，性能较低，建议改成显式JOIN写法，同时子查询里的关联条件可以统一对齐主表字段，避免逻辑错误。

内容的提问来源于stack exchange，提问作者RK-Rohan