mysql - 子查询的 where 子句出现查询错误

Question

我的查询有错误

这是我的查询

SELECT a.id_stock, a.color, a.size, (SUM(IFNULL(a.barang_masuk,0)) - SUM(IFNULL(a.reject,0)) - IFNULL((
    SELECT SUM(IFNULL(qty,0)) AS SUMqty FROM transaksi WHERE transaksi.id_stock = a.id_stock GROUP BY id_stock
),0)) AS sold FROM stock a
WHERE a.no_barang='10' AND sold>0
GROUP BY a.no_barang, a.color, a.size
ORDER BY color, size ASC

这是错误

#1054 - Unknown column 'sold' in 'where clause'

有什么建议可以解决这个问题吗？谢谢

Answer 1

自定义别名不被 where 子句处理，您需要为此使用 HAVING

SELECT a.id_stock, a.color, a.size, (SUM(IFNULL(a.barang_masuk,0)) - SUM(IFNULL(a.reject,0)) - IFNULL((
    SELECT SUM(IFNULL(qty,0)) AS SUMqty FROM transaksi WHERE transaksi.id_stock = a.id_stock GROUP BY id_stock
),0)) AS sold FROM stock a
HAVING a.no_barang='10' AND sold>0
GROUP BY a.no_barang, a.color, a.size
ORDER BY color, size ASC

对于这个问题，我猜你不能使用之前的分组依据，你可以使用子选择

SELECT q.* FROM (
    SELECT a.id_stock, a.color, a.size, (SUM(IFNULL(a.barang_masuk,0)) - SUM(IFNULL(a.reject,0)) - IFNULL((
        SELECT SUM(IFNULL(qty,0)) AS SUMqty FROM transaksi WHERE transaksi.id_stock = a.id_stock GROUP BY id_stock
    ),0)) AS sold FROM stock a

    HAVING a.no_barang='10' AND sold>0 
 /* you can use where and having both in same query like WHERE a.no_barang='10' HAVING sold>0 */

    ORDER BY color, size ASC ) q  GROUP BY q.no_barang, q.color, q.size

编辑

SELECT a.id_stock, a.color, a.size, (SUM(IFNULL(a.barang_masuk,0)) - SUM(IFNULL(a.reject,0)) - IFNULL((
    SELECT SUM(IFNULL(qty,0)) AS SUMqty FROM transaksi WHERE transaksi.id_stock = a.id_stock GROUP BY id_stock
),0)) AS sold FROM stock a
WHERE a.no_barang='10' 
GROUP BY a.no_barang, a.color, a.size
HAVING sold>0
ORDER BY color, size ASC

Unknown Column In Where Clause

How to use GROUP BY after the Having clause Mysql