php - codeigniter:按数据类别获取多个表中的百分比

Question

我有这样的表格，

table_a

PID  Name   Type
1    a      selling
2    b      sampling
3    c      selling
4    d      sampling
5    e      selling

并且，

table_b

ID      PID   Qty
1       2     3
2       2     2
3       1     1
4       3     1
5       1     3
6       5     3
7       4     3
8       3     2

我需要这样显示，

output_selling

Name           Total_qty     Percentage
a              4             40%
c              3             30%
e              3             30%

并且，

output_sampling

Name           Total_qty     Percentage
b              5             62,5%
d              3             37,5%

我该如何在 CodeIgniter 中解决这个问题？

Answer 1

您可以在函数中使用以下内容，并根据需要更改“采样”和“销售”:

$this->db->query("
SELECT dd.NAME, 
       dd.total_qty, 
       ( dd.total_qty / dd.totals ) * 100 AS Percentage 
FROM   (SELECT a.NAME, 
               Sum(b.qty)                            AS Total_qty, 
               (SELECT Sum(b1.qty) AS total 
                FROM   table_b b1 
                       JOIN table_a a1 
                         ON a1.pid = b1.pid 
                            AND a1.type = 'selling') AS totals 
        FROM   table_a a 
               JOIN table_b b 
                 ON b.pid = a.pid 
                    AND a.type = 'selling' 
        GROUP  BY a.pid) dd ");

请参阅下面的查询输出: