MySQL-Berechnung, um Gewinn oder Verlust aus 2 Tabellen (Käufe und Verkäufe) im First-In-First-Out-Modus zu ermitteln

Question

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

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 来做这件事，这需要太多时间。

Answer 1

按照我的理解直接回答你的问题 - 这个查询应该可以解决问题

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;

这将产生以下输出

项目	总销售额	total_purchases	利润
A	172	200	-28
B	45	220	-175
C	0	250	-250
D	0	180	-180