我对查询语句不满意，并且想知道是否还有其他更好的方法可以做到这一点：
SELECT product.product,
COALESCE((SELECT SUM(qty) FROM stocktrxn
INNER JOIN stock on stock.tra_no = stocktrxn.tra_no
WHERE product_no = s1.product_no and stock.is_out = false) -
(SELECT SUM(qty) FROM stocktrxn
INNER JOIN stock on stock.tra_no = stocktrxn.tra_no
WHERE product_no = s1.product_no and stock.is_out = true), 0)
as qty
FROM stocktrxn s1
RIGHT JOIN product on product.id = s1.product_no
GROUP BY product ASC;
我认为您只需要条件聚合。像这样的东西：
SELECT p.product,
(SUM(CASE WHEN s.is_out = false THEN qty ELSE 0 END) -
SUM(CASE WHEN s.is_out = true THEN qty ELSE 0 END) -
) as qty
FROM product LEFT JOIN
stocktrxn st
ON p.id = st.product_no LEFT JOIN
stock s
ON s.tra_no = st.tra_n
GROUP BY p.product ASC;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句