php - 如何将 3 个表与 2 个表的数据总和连接起来

Question

我有 3 张 table 。购买订单、 cargo 接收、购买交易。

*** 我的购买订单 ID 显示为 order_id。 1.我需要将goods_receive表中的所有数量求和作为订单id。 2.其次，我需要购买交易表中的总金额作为订单ID。

查看我的附件图片更清楚>>

<?php


include("db.php");



$sqlnew="SELECT purchase_order.id , purchase_order.quantity,
 purchase_order.unit_price, purchase_order.total_amount, 
 sum(goods_receive.quantity) as qua , sum(purchase_transaction.amount) as amount 
      
       
  FROM purchase_order
 JOIN goods_receive  ON purchase_order.id = goods_receive.order_id


 JOIN purchase_transaction ON purchase_order.id = goods_receive.order_id

 GROUP BY goods_receive.order_id";

$resultnew=mysql_query($sqlnew);



?>

            <table class="table table-bordered data-table" border="1">
              <thead>
                <tr>
				<th>Order ID</th>
				
           
                  <th>Quantity</th>
				   <th> Receive Quantity</th>
                  <th>Unit Price</th>
				  <th>Total Amount</th>
				   <th>Paid Amount</th>
				  
		
				
                </tr>
              </thead>
              <tbody>
			  <?php
while($rows=mysql_fetch_array($resultnew)){
?>
                <tr class="gradeX">
				 <td><a href="purchase_order_single_details.php?id=<?php echo $rows['id']; ?>"><?php echo $rows['id']; ?></td>
				
                  <td><?php echo $rows['quantity']; ?></td>
				       <td><?php echo $rows['qua']; ?></td>
                  <td><?php echo $rows['unit_price']; ?></td>
                  <td><?php echo $rows['total_amount']; ?></td>
			<td><?php echo $rows['amount']; ?></td>
			

				
				</tr>
				
				
				
				
				<?php
}
?>

				
				
             
               </tbody>
            </table>
				
          </div>
        </div>
		
		
		
		
		
      </div>
    </div>
  </div>
</div>
<!--Footer-part-->

请帮助我...

Answer 1

加入子查询来计算您想要的总计

SELECT po.order_id, po.quantity, gr.quantity AS receive_quantity, 
    po.unit_price, po.total_amount, pt.amount AS paid_amount
FROM purchase_order AS po
JOIN (SELECT order_id, SUM(quantity) AS quantity
      FROM good_receive
      GROUP BY order_id) AS gr ON gr.order_id = po.order_id
JOIN (SELECT order_id, SUM(amount) AS amount
      FROM purchase_transaction
      GROUP BY order_id) AS pt ON pt.order_id = po.order_id