2개의 테이블(구매 및 판매)에서 선입선출 모드로 손익을 구하는 mysql 계산
<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>
