php - 使用 sum，group by 从不同的表访问查询

Question

我在名为“tbl_stock”和“tbl_sale”的两个表上有以下列和数据

• tbl_stock

ID	stock_yard_name	stock_net_weight
1	阿萨姆手镯	31000
2	巴瑞克大厦	32000
3	阿萨姆手镯	32050

• tbl_sale

ID	sale_yard_name	sale_net_weight
1	阿萨姆手镯	31000
2	巴瑞克大厦	31200

我想像图片一样做一个总结 图片链接

如何正确查询

这是我尝试过的方法

<?php
    $query  = "SELECT stock_yard_name, SUM(stock_net_weight) AS stock_net, SUM(tbl_sale.sale_net_weight) AS sale_net
              FROM tbl_stock LEFT JOIN tbl_sale
              ON tbl_stock.stock_yard_name = tbl_sale.sale_yard_name
              GROUP BY stock_yard_name
              ";
$post = $db->select($query);
        if ($post) {
          while ($result = $post->fetch_assoc()) {
?>
<tr>
  <td>
    <label><?php echo $result['stock_yard_name']; ?></label>
  </td>
  <td>
    <?php echo $result['stock_net']; ?><span> KG</span>
  </td>
  <td>
    <?php echo $result['sale_net']; ?><span> KG</span>
  </td>
  <td>
    <?php $balanced = ($result['stock_net']-$result['sale_net']); ?>
    <?php echo $balanced; ?><span> KG</span>
  </td>
</tr>
<?php }} ?>
</tbody>

标签： phpsql

Answer 1

这应该给你你正在寻找的东西。

    select distinct stock_yard_name, stock, sale
    from (
    select stock_yard_name , sum(stock_net_weight)  stock
    from table1 
    group by stock_yard_name) t1 
    join (
    select sale_yard_name,  sum( sale_net_weight) sale
    from  table2
      group by sale_yard_name
    ) t2 on stock_yard_name = sale_yard_name