將"Operator value in where condition"翻譯成中文為"在where條件中的運算子值"

Question

我該如何做到這一點？

SELECT sum(buy-sale) as istock form tbl_product
where istock > 0

我對以下方法不感興趣。

SELECT sum(buy-sale) as istock form tbl_product
where sum(buy-sale)> 0

Answer 1

你只需要將WHERE替換為HAVING ;-)

SELECT SUM(`buy-sale`) AS `istock` FROM tbl_product HAVING `istock` > 0

但是這將傳回所有記錄的總和。通常你在按照某個條件進行分組時才會這樣做，例如按產品ID或其他條件。例如：

-- 查找大订单
SELECT 
    `order_number`,
    SUM(`quantity_ordered`) AS `quantity_sum`,
    SUM(`product_price` * `quantity_ordered`) AS `total`
FROM
    order_details
GROUP BY 
   `order_number`
HAVING 
   `total` > 1000;

這可能會有所幫助：https://www.mysqltutorial.org/mysql-having.aspx