php – mySQL中的条件求和？

我不是很擅长SQL,所以我一直试图通过 PHP处理这个问题并做非常基本的mySQL查询,但显然我的内存不足(在PHP中)因为我处理了大量数据(并做了大量可怕的嵌套foreach’s).我希望可能有一种方法可以重新编写我的SQL以使其不会发生.

这是我正在处理的表格(仅显示相关字段)：

组织

> OrgID

FundingCycles

> CycleID
> NonUnityCut??BackAmount
> UnityCut??BackAmount

AdministrativeRequests

> CycleID
> OrgID
> AmountRequested
> AmountFunded
> PerOff

CapitalRequests

> CycleID
> OrgID
> AmountRequested
> AmountFunded
> PerOff

EventRequests

> CycleID
> OrgID
> AmountRequested
> AmountFunded
> PerOff
> UnityReq

所以,这是复杂的部分.我需要为AmountRequested,AmountFunded的每个组织创建总和(这两个似乎很容易,虽然不确定跨多个表的求和),以及来自所有请求表(AdministrativeRequests,CapitalRequests,EventRequests)中的请求的计算字段AmountAfterCutback ).

AmountAfterCutback等于：AmountFunded – (AmountFunded * Cutback).

如果削减只是一个值,这可能很简单.但是,Cutback来自FundingCycles表.所以我不能直接求和并应用削减,我必须找出每个请求的削减,并且为此我必须查看每个请求,然后通过其CycleID检查并查看该请求的削减在FundingCycles表中.

默认情况下,Cutback将等于NonUnityCut??Back.但是,如果请求是Unity请求(仅限EventRequests,UnityReq == 1),则削减等于UnityCut??Back.然而,它变得更具挑战性,因为削减可以单独应用于每个请求,覆盖统一或不一致的削减(如果PerOff> 0).

所以,不知何故,我必须总结一个组织所做的每一个请求,并正确计算上面提到的字段.我将在下面展示我的解决方案,我将警告它肯定不是很漂亮,并且在这一点上甚至没有功能,因为它在尝试通过PHP处理它时耗尽了内存.我知道有更好的方法来做到这一点.有些方法可以对每个请求进行条件计算,这样可以在查询中求和,而不是我下面笨重的PHP解决方案？有任何想法吗？感谢任何有耐心甚至读到这一点的人,我知道这是一个问题的PITA.任何帮助是极大的赞赏！

function calculate_public_records()
{
      $total_req_total = 0;
      $total_funded_total = 0;
      $total_cutback_total = 0;

      $organizations = array();

      foreach($this->organizations as $org)
      {
          $id = $org['OrgID'];
          $org_req_total = 0;
          $org_funded_total = 0;
          $org_cutback_total = 0;

          $cycles = array();

          foreach($this->cycles as $cycle)
          {
                $cycle_req_total = 0;
                $cycle_funded_total = 0;
                $cycle_cutback_total = 0;

                $cycle_requests = array();

                foreach($this->request_types as $type)
                {
                    $reqs = $this->funding_request_model->getRequests($type,$cycle['CycleID'],$id);
                    foreach($reqs as $r)
                    {
                        $cutback = $cycle['NonUnityCutBack'];
                        if ($type == "Event") $cutback = ($r->UnityReq == 1) ? $cycle['UnityCutBack'] : $cutback;
                        $cutback = ($r->PerOff != 0) ? $r->PerOff : $cutback;

                        $request = array();
                        $request['id']        = $r->ReqID;
                        $request['type']      = $type;
                        $request['name']      = $r->Title;
                        $request['requested'] = number_format($r->RequestTotal,2);
                        $request['funded']    = number_format($r->AmtFunded,2);
                        $request['cutback']   = number_format(($r->AmtFunded - ($r->AmtFunded * $cutback)),2);

                        $cycle_req_total += $request['requested'];
                        $cycle_funded_total += $request['funded'];
                        $cycle_cutback_total += $request['cutback'];

                        $cycle_requests[] = $request;
                    }

                }

                $cycle_totals = array();
                $cycle_totals['requested'] = number_format($cycle_req_total,2);
                $cycle_totals['funded'] = number_format($cycle_funded_total,2);
                $cycle_totals['cutback'] = number_format($cycle_cutback_total,2);

                $org_req_total += $cycle_req_total;
                $org_funded_total += $cycle_funded_total;
                $org_cutback_total += $cycle_cutback_total;

                $cycles[] = array('name' => $cycle['CycleName'],'requests' => $cycle_requests,'totals' => $cycle_totals);
          }

          $org_totals = array();
          $org_totals['requested'] = number_format($org_req_total,2);
          $org_totals['funded'] = number_format($org_funded_total,2);
          $org_totals['cutback'] = number_format($org_cutback_total,2);

          $total_req_total += $org_req_total;
          $total_funded_total += $org_funded_total;
          $total_cutback_total += $org_cutback_total;

          $organizations[] = array('id' => $org['OrgID'],'name' => $org['Organization'],'cycles' => $cycles,'totals' => $org_totals);
      }

      $totals = array();
      $totals['requested'] = number_format($total_req_total,2);
      $totals['funded'] = number_format($total_funded_total,2);
      $totals['cutback'] = number_format($total_cutback_total,2);

      return array('organizations' => $organizations,'totals' => $totals);

计算摘要：

# For tables AdministrativeRequests,CapitalRequests & EventRequests,calculate:

    SUM(AmountRequested)
    SUM(AmountFunded)

# For tables AdministrativeRequests,calculate:

    AmountAfterCutback = AmountFunded - (AmountFunded * Cutback)

# Cutback calculation pseudocode

if( <table>.PerOff > 0 ) {
    Cutback = <table>.PerOff
} elseif ( <table> == EventRequests && EventRequests.UnityReq == 1 ) {
    Cutback = FundingCycles.UnityCutBack
} else {
    Cutback = FundingCycles.NonUnityCutBack
}

SELECT OrgID,SUM(AmountRequested),SUM(AmountFunded),SUM(AmountFunded - (
           AmountFunded * 
           IF( PerOff > 0,PerOff,IF( UnityReq = 1,UnityCutBackAmount,NonUnityCutBackAmount
               )
           )
       )) AS AmountAfterCutback
  FROM (
SELECT o.OrgID,ar.AmountRequested,ar.AmountFunded,ar.PerOff,null AS UnityReq,fc_ar.CycleID,fc_ar.NonUnityCutBackAmount,fc_ar.UnityCutBackAmount
  FROM Organizations o
  JOIN AdministrativeRequests ar
    ON ar.OrgID = o.OrgID
  JOIN FundingCycles fc_ar
    ON fc_ar.CycleID = ar.CycleID
 UNION ALL
SELECT o.OrgID,cr.AmountRequested,cr.AmountFunded,cr.PerOff,fc_cr.CycleID,fc_cr.NonUnityCutBackAmount,fc_cr.UnityCutBackAmount
  FROM Organizations o
  JOIN CapitalRequests cr
    ON cr.OrgID = o.OrgID
  JOIN FundingCycles fc_cr
    ON fc_cr.CycleID = cr.CycleID
 UNION ALL
SELECT o.OrgID,er.AmountRequested,er.AmountFunded,er.PerOff,er.UnityReq,fc_er.CycleID,fc_er.NonUnityCutBackAmount,fc_er.UnityCutBackAmount
  FROM Organizations o
  JOIN EventRequests er
    ON er.OrgID = o.OrgID
  JOIN FundingCycles fc_er
    ON fc_er.CycleID = er.CycleID
) tmp
 GROUP BY OrgID;