<p>如何从这两个表计算利润或损失？</p> <pre class="brush:php;toolbar:false;">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');</pre> <table class="s-table"> <thead> <tr> <th>id</th> <th>姓名</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>一个</td> </tr> <tr> <td>2</td> <td>B</td> </tr> <tr> <td>3</td> <td>C</td> </tr> <tr> <td>4</td> <td>D</td> </tr> </tbody> </table> <pre class="brush:php;toolbar:false;">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);</pre> <table class="s-table"> <thead> <tr> <th>id</th> <th>item_id</th> <th>qnt</th> <th>成本</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>10</td> <td>20</td> </tr> <tr> <td>2</td> <td>2</td> <td>10</td> <td>10</td> </tr> <tr> <td>3</td> <td>3</td> <td>10</td> <td>10</td> </tr> <tr> <td>4</td> <td>4</td> <td>10</td> <td>10</td> </tr> <tr> <td>5</td> <td>1</td> <td>10</td> <td>25</td> </tr> <tr> <td>6</td> <td>2</td> <td>10</td> <td>16</td> </tr> </tbody> </table> <pre class="brush:php;toolbar:false;">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);</pre> <table class="s-table"> <thead> <tr> <th>id</th> <th>item_id</th> <th>qnt</th> <th>价格</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>2</td> <td>25</td> </tr> <tr> <td>2</td> <td>2</td> <td>3</td> <td>15</td> </tr> <tr> <td>3</td> <td>1</td> <td>3</td> <td>26</td> </tr> <tr> <td>4</td> <td>1</td> <td>2</td> <td>22</td> </tr> </tbody> </table> <p>我需要的是将采购表中每件商品的成本像这样，(salesprice<em>qnt)-( costprice</em>qnt) 作为利润/损失</p> <table class="s-table"> <thead> <tr> <th>id</th> <th>item_id</th> <th>qnt</th> <th>价格</th> <th>成本</th> <th>利润</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>2</td> <td>25</td> <td>40</td> <td>10</td> </tr> <tr> <td>2</td> <td>2</td> <td>3</td> <td>15</td> <td>30</td> <td>15</td> </tr> <tr> <td>3</td> <td>1</td> <td>3</td> <td>26</td> <td>60</td> <td>18</td> </tr> <tr> <td>4</td> <td>1</td> <td>2</td> <td>22</td> <td>40</td> <td>4</td> </tr> </tbody> </table> <p>不知道该怎么做，现在我正在使用 PHP 来做这件事，这需要太多时间。</p>

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;