php - 如何进行查询以获得可区分的值？

Question

我有一个包含 4 个表的应用程序:

Employees -> Companies -> Additionals -> Debts

我需要生成以下工资收据:

Receipt Example

在这份文件中，我必须显示所有歧视性信息，例如“工资”及其值(value)和费用。最后是附加额和债务之间的净值。

我做了这个例子:

$total_additional= 0;
$total_debt= 0;

// Here in employees contains a join between employees and companies
foreach ($employees as $employee) {

  foreach ($additionals as $additional) {
    if ( $employee->id == $additional->employee_id ) {
     //Print the additional reference
     $total_additional += $additional->value;
    }
  }

  foreach ($debts as $debt) {
    if ( $employee->id == $debt->employee_id ) {
     //Print the debt reference
     $total_debt += $debt->value;
    }
  }

  // Here should come the net value

}

如何进行查询来获取这些结果？

Answer 1

如何使用一些 LEFT JOIN、GROUP BY 子句和 SUM 聚合来获取一个查询中的所有数据，而无需循环PHP？

SELECT employees.id, employees.name, 
    GROUP_CONCAT(additionals.value SEPARATOR '|') as additionals_values, SUM(additionals.value) as total_additional
FROM employees
LEFT JOIN additionals ON additionals.employee_id = employees.id
GROUP BY employees.id, employees.name;

SELECT employees.id, employees.name, 
    GROUP_CONCAT(debts.value SEPARATOR '|') as debts_values, SUM(debts.value) as total_debt
FROM employees
LEFT JOIN debts ON debts.employee_id = employees.id
GROUP BY employees.id, employees.name;

在 php 中使用 explode('|'... 来获得一组单独的值。可以使用第三个查询或在 PHP 中计算净值。