使用mysql保留每种产品的总购买量、销售额和库存

Question

我正在尝试使用mysql查询获取reach产品的总购买量、销售额和剩余sotck，如下所示：

select fk_product_id,
(select sum(quantity) from entry_products where status =0 ) as total_purchase,
(select sum(quantity) from entry_products where status =1)as total_sales,
(select sum(quantity) from entry_products where status =0 ) -
(select sum(quantity) from entry_products where status =1) as stock

from entry_products group by fk_product_id

输出

fk_product_id     total_purchase    total_sales     stock
1                   1700                 660         1040
2                   1700                 660         1040
3                   1700                 660         1040

My Expected Output is
fk_product_id     total_purchase    total_sales     stock
1                   350                  200         150
2                   1100                 460         640
3                   250                  0           250

Answer 1

您需要条件聚合：

select fk_product_id,
       sum(case when status = 0 then quantity else 0 end) as total_purchase,
       sum(case when status = 1 then quantity else 0 end) as total_sales,
       sum(case when status = 0 then quantity else 0 end) - sum(case when status = 1 then quantity else 0 end) as stock
from entry_products 
group by fk_product_id

由于MySql将布尔表达式计算为1 (对于true )或0 (对于false )，因此代码也可以这样编写：

select fk_product_id,
       sum((status = 0) * quantity) as total_purchase,
       sum((status = 1) * quantity) as total_sales,
       sum((status = 0) * quantity) - sum((status = 1) * quantity) as stock
from entry_products 
group by fk_product_id