mysql - 我们如何从 Cakephp 中的两个表中减去两列的总和

Question

我有两个名为 SalaryPayment 和 SalaryStaff 的表。

员工薪资表

+-----------------+------------+-------------+
| SalaryStaffId   | EmployeeId | TotalWage   |    
+-----------------+------------+-------------+
|      6          |    5       |    80       |  
|      7          |    5       |    100      |
|      9          |    5       |    60       | 
+-----------------+------------+-------------+

工资支付表

+-----------------+------------+-------------+
| SalaryPaymentId | EmployeeId | PaidAmount  |    
+-----------------+------------+-------------+
|      1          |    5       |    550      |  
+-----------------+------------+-------------+

我需要如下结果，

+-----------------+-----------------+-----------------+-----------------+
|  EmployeeId     | TotalPaidAmount | TotalWageAmount | PendingAmount   |
+-----------------+-----------------+-----------------+-----------------+
|      5          |    550          |    240          |     210         |
+-----------------+-----------------+-----------------+-----------------+

TotalPaidAmount means SUM(SalaryPayment.PaidAmount )

TotalWageAmount means SUM(SalaryStaff.TotalWage )

PendingAmount means SUM(SalaryStaff.TotalWage ) - SUM(SalaryPayment .PaidAmount )

我尝试了以下方法，它使 TotalPaidAmount 获得实际值的三倍，即 1650，并且 TotalWageAmount 获得正确的值。

$this->SalaryStaff->find('all',array(
                'fields'=>array('SalaryPayment.*','SalaryStaff.*','SUM(SalaryStaff.TotalWage) as TotalWageAmount','SUM(SalaryPayment.PaidAmount) as TotalPaidAmount'),
                'joins'=>array(
                    array(                      
                        'alias'=>'SalaryPayment',
                        'table'=>'salary_payments',                     
                        'conditions' =>array('SalaryPayment.EmployeeId = SalaryStaff.EmployeeId')
                        )
                    ),
                'conditions' =>array('SalaryStaff.EmployeeId'=>$EmployeeId),
                'group' => 'SalaryStaff.EmployeeId'
                ));

Answer 1

对于您的具体问题，您最好只运行两个 find 来检索该员工所需的两个值。

要实现单个查询解决方案，最简单的方法可能是先了解如何编写 SQL，然后再弄清楚 Cakephp。每个表的行需要首先“分组”，然后“加入”。

SELECT SalaryStaffGrouped.EmployeeId
     , COALESCE(TotalPaidAmount, 0) AS TotalPaidAmount
     , TotalWageAmount
     , COALESCE(TotalPaidAmount,0)-TotalWageAmount AS PendingAmount
FROM (SELECT EmployeeId, SUM(TotalWage) AS TotalWageAmount
      FROM SalaryStaff
      GROUP BY EmployeeId) AS SalaryStaffGrouped
LEFT JOIN (SELECT EmployeeId, SUM(PaidAmount) AS TotalPaidAmount
      FROM SalaryPayment) AS SalaryPaymentGrouped
  ON SalaryStaffGrouped.EmployeeID = SalaryPaymentGrouped.EmployeeId;

有几种方法可以处理 Cakephp 中的实现。我会推荐query method为了方便。您还可以基于这些分组/汇总表创建一些 MySQL View ，然后基于这些 View 构建 Cakephp 模型。

<小时/>

您需要注意，这只会列出在您的 SalaryStaff 表中找到的员工。如果该员工仅存在于 SalaryPayment 表(而不是 SalaryStaff 表)中，则不会列出此人。

<小时/>

如果您需要按 EmployeeId 进行过滤，则通过添加两个 WHERE 子句(每个子查询一个子句，而不是整个查询)，查询将获得更好的性能。