php - 使用多个条件求和数组值

Question

我试图根据客户和日期范围获取数组中所有“金额”元素的总和。我希望输出数组(最终结果)看起来像这样......

Array
(
[0] => stdClass Object
    (
        [company_name] => Yeung's Architects
        [customer_id] => 3
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 1543.64
        [account_id] => 75
    )

[1] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2177.27
        [account_id] => 75
    )

[2] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 295.45
        [account_id] => 75
    )
 )

这是我正在使用的 sql 调用...

SELECT c.company_name
     , i.customer_id
     , a.name
     , i.due_date
     , li.amount
     , li.account_id 
  FROM invoices i 
  JOIN customers c 
    ON c.customer_id = i.customer_id
  JOIN invoices_line_items li 
    ON li.guid = i.guid 
   AND li.invoice_id = i.invoice_id
  JOIN accounts a 
    ON a.guid = li.guid 
   AND a.account_id = li.account_id
 WHERE i.guid = ? 
   AND i.date >= "'.$firstOfThisMonth.'" ',array($guid));

从 sql 调用生成的数组开始使用...

Array
(
[0] => stdClass Object
    (
        [company_name] => Yeung's Architects
        [customer_id] => 3
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 1362.73
        [account_id] => 75
    )

[1] => stdClass Object
    (
        [company_name] => Yeung's Architects
        [customer_id] => 3
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 180.91
        [account_id] => 75
    )

[2] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 177.27
        [account_id] => 75
    )

[3] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 159.09
        [account_id] => 75
    )

[4] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 136.36
        [account_id] => 75
    )

[5] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Services
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2000.00
        [account_id] => 1
    )

   )

我当前正在使用的代码尝试根据客户 ID 和发票月份对所有金额进行求和。

            $output = array($data[0]); $c = 1;
            foreach ($data as $key => $value) {
                if ($key > 0) {

                    if (in_array($value->customer_id,array_column($output, 'customer_id'))) {

                        $valueDate = substr($value->due_date,0,7);
                        foreach ($output as $k => $v) {
                            if ($v->customer_id == $value->customer_id) {
                                $vDate = substr($v->due_date,0,7);
                                if ($vDate == $valueDate) {
                                    $output[$k]->amount = $output[$k]->amount + $value->amount;
                                } else {
                                    $output[$c] = $value;
                                    $c++;
                                }

                            }
                        }

                    } else {
                        $output[$c] = $value;
                        $c++;
                    }

                }
            }

            return $output;

这是我从上述代码中得到的最终结果...

Array
(
[0] => stdClass Object
    (
        [company_name] => Yeung's Architects
        [customer_id] => 3
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 1543.64
        [account_id] => 75
    )

[1] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2177.27
        [account_id] => 75
    )

[2] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 295.45
        [account_id] => 75
    )

[3] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 136.36
        [account_id] => 75
    )

[4] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Services
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2000.00
        [account_id] => 1
    )

[5] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Services
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2000.00
        [account_id] => 1
    )

   )

前 3 个键是正确的结果，但随后添加了 3 个额外的键，我不确定它们是如何添加的。我的大脑现在坏了，请有人帮助我，任何帮助将不胜感激。

Answer 1

因此，如果我关注您，您只想按 company_name 对其进行分组，还是还有其他维度？在 SQL 中执行此操作实际上非常简单。

使用聚合函数、总和并按公司字段分组进行查询。您可以在下面看到几乎没有变化。

SELECT c.company_name
     , i.customer_id
     , a.name
     , i.due_date
     , SUM(li.amount)
     , AVG(li.amount)
     , li.account_id 
  FROM invoices i 
  JOIN customers c 
    ON c.customer_id = i.customer_id
  JOIN invoices_line_items li 
    ON li.guid = i.guid 
   AND li.invoice_id = i.invoice_id
  JOIN accounts a 
    ON a.guid = li.guid 
   AND a.account_id = li.account_id
 WHERE i.guid = ? 
   AND i.date BETWEEN :startDate AND :endDate
 GROUP BY i.customer_id

更多信息可以在这里找到:https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html在这里https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

如果需要，您可以添加第二个 GROUP BY(您真正想要的数量)。假设您需要一个查询来获取“按客户、按月计算的总和”。你会做类似的事情...

 GROUP BY i.customer_id, DATE_FORMAT(i.due_date, '%Y-%m')

WITH ROLLUP 在调试时很有用。