mysql計算以先進先出模式從2個表（採購和銷售）中獲取利潤或損失-PHP中文網路問答

如何从这两个表计算利润或损失？

create table items(id int primary key auto_increment, name varchar(255)); insert into items value(null,'A'); insert into items value(null,'B'); insert into items value(null,'C'); insert into items value(null,'D');

id	姓名
1	一个
2	B
3	C
4	D

create table purchase(id int primary key auto_increment, item_id int,qnt int,price int); insert into purchase value(null,1,10,20); insert into purchase value(null,2,10,22); insert into purchase value(null,3,10,25); insert into purchase value(null,4,10,18); insert into purchase value(null,5,10,25);

id	item_id	qnt	成本
1	1	10	20
2	2	10	10
3	3	10	10
4	4	10	10
5	1	10	25
6	2	10	16

create table sales(id int primary key auto_increment, item_id int,qnt int,price int); insert into purchase value(null,1,2,25); insert into purchase value(null,2,3,15); insert into purchase value(null,1,3,26); insert into purchase value(null,1,2,22);

id	item_id	qnt	价格
1	1	2	25
2	2	3	15
3	1	3	26
4	1	2	22

我需要的是将采购表中每件商品的成本像这样，(salespriceqnt)-( costpriceqnt) 作为利润/损失

id	item_id	qnt	价格	成本	利润
1	1	2	25	40	10
2	2	3	15	30	15
3	1	3	26	60	18
4	1	2	22	40	4

不知道该怎么做，现在我正在使用 PHP 来做这件事，这需要太多时间。

select items.name as item, coalesce(sales_totals.total, 0) as total_sales, coalesce(purchases_totals.total, 0) as total_purchases, coalesce(sales_totals.total, 0) - coalesce(purchases_totals.total, 0) as profit from items left join ( select item_id, sum(qnt*price) as total from purchase group by item_id ) purchases_totals on purchases_totals.item_id = items.id left join ( select item_id, sum(qnt*price) as total from sales group by item_id ) sales_totals on sales_totals.item_id = items.id;