我在我的数据库中有一个表( sales ),它记录了销售和销售退货。

销售退货是用status='sr'记录的,我想在扣除销售退货后显示每个产品的最终数量。例如,对于产品B0801180323NA,有quantity:2的销售退货,也有quantity:2的销售,所以现在必须显示的最终数量是0。
发布于 2018-01-29 17:58:17
SELECT products, SUM(IF(status='sale',quantity,0)) - SUM(IF(status='sr',quantity,0)) AS quantity
FROM sales
GROUP BY bill_id, products;这将根据“status”列计算数量,从“sr”列中减去“sale”数量。
发布于 2018-01-29 17:48:43
您可以对sum group by products使用相同的表和不同的连接
select a.products, sum(a.quantity) - ifnull(sum(b.quantity),0)
from sales
left join sales on a.products = b.products and b.status = 'sr'
where a.status = 'sale'
group by a.products发布于 2018-01-29 17:57:56
我会使用子查询
select products,
(
select sum(quantity) from sales s2 where s2.products = s1.products and status = 'sale'
) -
(
select sum(quantity) from sales s2 where s2.products = s1.products and status = 'sr'
)
from (select distinct products from sales) s1https://stackoverflow.com/questions/48498291
复制相似问题