php - 在 MySQL 中按年和月分组

Question

我有一个包含 25 个日期列的表格。现在我正在用 PHP 构建一个应用程序，并且必须显示基于月份和年份的摘要。我在下面提到了源表布局和预期的摘要表。

这里 ID 代表每一行。源表 ID 将在汇总表中使用。汇总表将每年创建一次。我需要一些帮助来准备这个汇总表。在这里我想提一下，我将在源表上有大约 30K 行。

源表:

ID  |  DATE 1      |  DATE 2        |   DATE 3
--------------------------------------------------
1   |  2017-01-14  |   2017-01-19   |   2017-01-25
2   |              |   2017-03-19   |   2017-03-25
3   |  2017-03-15  |                |   2017-05-25
4   |  2017-04-24  |   2017-05-19   |   
5   |  2017-04-10  |   2017-06-19   |   2017-07-25
6   |  2017-05-11  |   2017-06-19   |   2017-08-25

汇总表

ID  |   YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC
------------------------------------------------------------------------------------
1   |   2017 |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0 
2   |   2017 |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0 
3   |   2017 |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0 
4   |   2017 |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0 
5   |   2017 |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0 
6   |   2017 |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0  |  0

Answer 1

您需要取消数据透视并重新聚合它。查询的结构是:

select id, year(date) as yyyy,
       sum( month(date) = 1 ) as Jan,
       sum( month(date) = 2 ) as Feb,
       . . .
       sum( month(date) = 12 ) as Dec
from ((select id, date1 as date from source) union all
      (select id, date2 as date from source) union all
      . . .
      (select id, date25 as date from source) 
     ) d
where date is not null
group by id, year(date)
order by id, year(date);

。 . . 表示填空。