>我的查询有误
这是我的查询
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'
有什么建议可以解决这个问题吗??谢谢
自定义别名不按您需要为此使用HAVING
的where子句处理
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
未知列在 where 子句中
如何在 Have 子句 Mysql 之后使用 GROUP BY