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

Question

我想得到两年之间的所有月份，以及两年之间的所有月份，他们的所有数据都将相加，以得到每年的销售额。

以下是我的表格截图：
截图

例如，我想获得2016年至2017年的年销售额，我需要将名称中包含2016年和2017年的所有列相加，以获得年销售额。我知道这张表不是正常的或正常的。但是这张桌子不是我的。

以下是获取两个日期之间月份的示例代码：

$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

在某个时刻，你会后悔有一张很难查询的表。我不知道您是否想为此利用动态sql，但下面的内容可能会有用。

SQL小提琴

MySQL 5.6架构设置：

CREATE TABLE Table1
    (`DEC_2016` int, `JAN_2017` int, `FEB_2017` int, `MAR_2017` int, `APR_2017` int, `MAY_2017` int, `JUN_2017` int, `JUL_2017` int, `AUG_2017` int, `SEP_2017` int, `OCT_2017` int, `NOV_2017` int, `DEC_2017` int, `JAN_2018` int, `FEB_2018` int, `MAR_2018` int, `APR_2018` int, `MAY_2018` int, `JUN_2018` int, `JUL_2018` int, `AUG_2018` int, `SEP_2018` int, `OCT_2018` int, `NOV_2018` int, `DEC_2018` int, `JAN_2019` int)
;

INSERT INTO Table1
    (`DEC_2016`, `JAN_2017`, `FEB_2017`, `MAR_2017`, `APR_2017`, `MAY_2017`, `JUN_2017`, `JUL_2017`, `AUG_2017`, `SEP_2017`, `OCT_2017`, `NOV_2017`, `DEC_2017`, `JAN_2018`, `FEB_2018`, `MAR_2018`, `APR_2018`, `MAY_2018`, `JUN_2018`, `JUL_2018`, `AUG_2018`, `SEP_2018`, `OCT_2018`, `NOV_2018`, `DEC_2018`, `JAN_2019`)
VALUES
    (1000, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 100)
;

查询1：

set @start := '2017-01-15'

select concat(
   'select '
  ,(select group_concat(' ', fn, ' as ', fn)
    from (
    select concat(upper(left(DATE_FORMAT(dt, "%M"),3)),'_',DATE_FORMAT(dt, "%Y")) fn
    from (
          SELECT
               n, DATE_FORMAT(@start, '%Y-%m-01') + interval n MONTH AS dt
          from (
               select 0 n union all  select 1 n union all  select 2 n union all  select 3 n union all  select 4 n union all  select 5 n union all  select 6 n union all  select 7 n union all  select 8 n union all  select 9 n union all  select 10 n union all  select 11
               ) d1
          ) d2
      ) d3
    )
  , ', ('
  ,  replace(
    (select trim(LEADING ' + ' from group_concat(' + ', fn))
      from (
      select concat(upper(left(DATE_FORMAT(dt, "%M"),3)),'_',DATE_FORMAT(dt, "%Y")) fn
      from (
            SELECT
                 n, DATE_FORMAT(@start, '%Y-%m-01') + interval n MONTH AS dt
            from (
                 select 0 n union all  select 1 n union all  select 2 n union all  select 3 n union all  select 4 n union all  select 5 n union all  select 6 n union all  select 7 n union all  select 8 n union all  select 9 n union all  select 10 n union all  select 11
                 ) d1
            ) d2
        ) d3
    ), ',','')
  , ') as Y1, '
  , (select group_concat(' ', fn, ' as ', fn)
    from (
    select concat(upper(left(DATE_FORMAT(dt, "%M"),3)),'_',DATE_FORMAT(dt, "%Y")) fn
    from (
          SELECT
               n, DATE_FORMAT(@start, '%Y-%m-01') + interval n MONTH AS dt
          from (
                 select 12 n union all  select 13 n union all  select 14 n union all  select 15 n union all  select 16 n union all  select 17 n union all  select 18 n union all  select 19 n union all  select 20 n union all  select 21 n union all  select 22 n union all  select 23
               ) d1
          ) d2
      ) d3
    )
  , ', ('
  ,  replace(
    (select trim(LEADING ' + ' from group_concat(' + ', fn))
      from (
      select concat(upper(left(DATE_FORMAT(dt, "%M"),3)),'_',DATE_FORMAT(dt, "%Y")) fn
      from (
            SELECT
                 n, DATE_FORMAT(@start, '%Y-%m-01') + interval n MONTH AS dt
            from (
                 select 12 n union all  select 13 n union all  select 14 n union all  select 15 n union all  select 16 n union all  select 17 n union all  select 18 n union all  select 19 n union all  select 20 n union all  select 21 n union all  select 22 n union all  select 23
                 ) d1
            ) d2
        ) d3
    ), ',','')
 , ') as Y2 '
 , ' from table1'
 ) as the_sql

结果：

| the dynamically generated sql is:
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| select  JAN_2017 as JAN_2017, FEB_2017 as FEB_2017, MAR_2017 as MAR_2017, APR_2017 as APR_2017, MAY_2017 as MAY_2017, JUN_2017 as JUN_2017, JUL_2017 as JUL_2017, AUG_2017 as AUG_2017, SEP_2017 as SEP_2017, OCT_2017 as OCT_2017, NOV_2017 as NOV_2017, DEC_2017 as DEC_2017, (JAN_2017 + FEB_2017 + MAR_2017 + APR_2017 + MAY_2017 + JUN_2017 + JUL_2017 + AUG_2017 + SEP_2017 + OCT_2017 + NOV_2017 + DEC_2017) as Y1,  JAN_2018 as JAN_2018, FEB_2018 as FEB_2018, MAR_2018 as MAR_2018, APR_2018 as APR_2018, MAY_2018 as MAY_2018, JUN_2018 as JUN_2018, JUL_2018 as JUL_2018, AUG_2018 as AUG_2018, SEP_2018 as SEP_2018, OCT_2018 as OCT_2018, NOV_2018 as NOV_2018, DEC_2018 as DEC_2018, (JAN_2018 + FEB_2018 + MAR_2018 + APR_2018 + MAY_2018 + JUN_2018 + JUL_2018 + AUG_2018 + SEP_2018 + OCT_2018 + NOV_2018 + DEC_2018) as Y2  from table1 |

执行后，::

| JAN_2017 | FEB_2017 | MAR_2017 | APR_2017 | MAY_2017 | JUN_2017 | JUL_2017 | AUG_2017 | SEP_2017 | OCT_2017 | NOV_2017 | DEC_2017 | Y1 | JAN_2018 | FEB_2018 | MAR_2018 | APR_2018 | MAY_2018 | JUN_2018 | JUL_2018 | AUG_2018 | SEP_2018 | OCT_2018 | NOV_2018 | DEC_2018 | Y2 |
|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|----|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|----------|----|
|        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 | 12 |        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 |        1 | 12 |

Answer 2

这应该能给你想要的。

$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;