如何从这两个表计算利润或损失？
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 来做这件事，这需要太多时间。
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
1
3150
收藏
按照我的理解直接回答你的问题 - 这个查询应该可以解决问题
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;这将产生以下输出