合计mysql php,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, CapitalRequests & EventRequests, calculate:

AmountAfterCutback = AmountFunded - (AmountFunded * Cutback)

# Cutback calculation pseudocode

if(

Cutback =

} elseif (

Cutback = FundingCycles.UnityCutBack

} else {

Cutback = FundingCycles.NonUnityCutBack

}