php - 获取两年之间的月份并获取每年所有月份的总和

Question

我想获取两年之间的所有月份，并将两年之间的所有月份的所有数据相加，以获得每年的销售额。

这是我的表格的屏幕截图:
Screenshot

例如，我想获取 2016 年至 2017 年的年度销售额，我需要将名称中包含 2016 年和 2017 年的所有列相加，才能获取年度销售额。我知道这张表没有标准化或正常。但这张 table 不是我的。

这是我获取两个日期之间月份的示例代码:

$start = new DateTime($_POST["start"]);
$end = new DateTime($_POST["end"]);

$smonth = (int)$start->format('Y')*12+(int)$start->format('n');
$emonth = (int)$end->format('Y')*12+(int)$end->format('n');

$firstmonth = min($smonth, $emonth);
$lastmonth = max($smonth, $emonth);
$months = array();

for ($i = $firstmonth; $i <= $lastmonth; $i++) {
    $thism = new DateTime(sprintf('%04d-%02d-01', intdiv($i, 12), $i % 12));
    $months[] = strtoupper($thism->format('M_Y'));
}

$m_total = implode(',', preg_replace('/^(.*)$/', 'SUM($1) AS $1', $months));
$m_average = implode(',', preg_replace('/^(.*)$/', 'AVG($1) AS $1', $months));

Answer 1

这应该会给你你想要的。

$start = new DateTime('2016-04-20');
$end = new DateTime('2018-03-02');

$smonth = (int)$start->format('Y')*12+(int)$start->format('n');
$emonth = (int)$end->format('Y')*12+(int)$end->format('n');

$firstmonth = min($smonth, $emonth);
$lastmonth = max($smonth, $emonth);

$months = array();
$m_totals = array();

for ($m = $firstmonth; $m <= $lastmonth; $m++) {
    $year = intdiv($m, 12);
    $thism = new DateTime(sprintf('%04d-%02d-01', $year, $m % 12));
    $months[] = strtoupper($thism->format('M_Y'));
    if ($m % 12 == 0) {
        //      $m_totals[] = 'SUM(' . implode(',', $months) . ') AS SUM' . ($year - 1);
        $m_totals[] = 'SUM(' . implode(') + SUM(', $months) . ') AS SUM' . ($year - 1);
        $months = array();
    }
}
if (count($months)) $m_totals[] = 'SUM(' . implode(') + SUM(', $months) . ') AS SUM' . $year;
$m_total = implode(', ', $m_totals);
echo "$m_total\n";

对于我使用的开始和结束日期，这将输出:

SUM(APR_2016) + SUM(MAY_2016) + SUM(JUN_2016) + SUM(JUL_2016) + SUM(AUG_2016) + SUM(SEP_2016) + SUM(OCT_2016) + SUM(NOV_2016) + SUM(DEC_2016) AS SUM2016,
SUM(JAN_2017) + SUM(FEB_2017) + SUM(MAR_2017) + SUM(APR_2017) + SUM(MAY_2017) + SUM(JUN_2017) + SUM(JUL_2017) + SUM(AUG_2017) + SUM(SEP_2017) + SUM(OCT_2017) + SUM(NOV_2017) + SUM(DEC_2017) AS SUM2017,
SUM(JAN_2018) + SUM(FEB_2018) + SUM(MAR_2018) AS SUM2018

要获取起始年和结束年的所有月份，只需更改以下行:

$smonth = (int)$start->format('Y')*12+1;
$emonth = (int)$end->format('Y')*12+12;