php - 如何在 codeigniter 中加入三个不同的表并从中获取总和？

Question

我有这样一个案例，我想从 CodeIgniter 中的三个不同表中生成一个报告，然后从其他表中生成一个摘要，有关详细信息，请查看:

我有表 tbl_product :

+----------+----------+---------+
| id_prdct | product  | remark  |
+----------+----------+---------+
|    1     | marriage | remark1 |
|    2     | office   | remark2 |
|    3     | school   | remark3 |
+----------+----------+---------+

然后我有如下表tbl_sell:

+---------+----------+------------+------------+
| id_sell | id_prdct | item_sell  | price_sell |
+---------+----------+------------+------------+
|    1    |    1     | dress      | USD 500    |
|    2    |    1     | shoes      | USD 600    |
|    3    |    1     | decoration | USD 1500   |
|    4    |    2     | dress      | USD 300    |
|    5    |    2     | shoes      | USD 500    |
|    6    |    3     | shoes      | USD 900    |
+---------+----------+------------+------------+

和tbl_buy如下:

+--------+----------+------------+------------+
| id_buy | id_prdct | item_buy   | price_buy  |
+--------+----------+------------+------------+
|    1   |    1     | dress      | USD 250    |
|    2   |    1     | shoes      | USD 300    |
|    3   |    1     | decoration | USD 1000   |
|    4   |    2     | dress      | USD 200    |
|    5   |    2     | shoes      | USD 300    |
|    6   |    3     | shoes      | USD 500    |
+--------+----------+------------+------------+

在我的网页中，我想做出如下结果:

+----+----------+------------+----------+----------+
| No | Product  | Sum Sell   | Sum Buy  | Profit   |
+----+----------+------------+----------+----------+
|  1 | marriage | USD 2600   | USD 1550 | USD 1050 |
|  2 | office   | USD 800    | USD 500  | USD 300  |
|  3 | school   | USD 900    | USD 500  | USD 400  |
+----+----------+------------+----------+----------+

所以我想从tbl_sell和tbl_buy做一个总结，然后在我的网页上显示它们然后做一个总结(利润)作为我上面的最后一个表格.有什么建议可以解决我的案子吗？

谢谢...

============================================= ===========[更新答案]

我已经试过了，但仍然出错，对此代码有什么建议吗？

$d['data'] = $this->db->query("select sum(tbl_sell.price) AS total_sell, sum(tbl_buy.price) AS total_buy
            left join tbl_sell on tbl_sell.id_prdct=tbl_product.id_prdct
            left join tbl_buy on tbl_buy.id_prdct=tbl_product.id_prdct
            from tbl_product 
            WHERE tbl_sell.id_prdct=tbl_product.id_prdct AND tbl_buy.id_prdct=tbl_product.id_prdct ");

Answer 1

试试这个 sql:

SELECT p.id_prdct, 
       p.product, 
       sell.total, 
       buy.total 
FROM   tbl_product p 
       LEFT JOIN (SELECT id_prdct, 
                         Sum(price_sell) total 
                  FROM   tbl_sell 
                  GROUP  BY id_prdct) sell 
              ON p.id_prdct = sell.id_prdct 
       LEFT JOIN (SELECT id_prdct, 
                         Sum(price_buy) total 
                  FROM   tbl_buy 
                  GROUP  BY id_prdct) buy 
              ON p.id_prdct = buy.id_prdct;