mysql - 如何显示一年中的所有月份，无论MySQL中每月是否有数据

Question

我有一个表 view_remit，其中包含 ID 列表及其各自的 total_PBR 每个月的第一天 (E_FROM) 到月底 (E_TO)。

|  ID  |  E_From  |   E_To   |Total_PBR|
|  01  |01/01/2016|03/31/2016|  1000   |
|  01  |08/01/2016|08/31/2016|  1000   |
|  02  |01/01/2016|01/31/2016|  3000   |
|  02  |02/01/2016|02/29/2016|  3000   |

我想要的是选择每个 ID 付款和不付款的所有月份。就像下面这个例子一样。

|  ID  |  E_From  |   E_To   |Total_PBR|
|  01  |01/01/2016|01/31/2016|  1000   |
|  01  |02/01/2016|02/29/2016|  1000   |
|  01  |03/01/2016|03/31/2016|  1000   |
|  01  |04/01/2016|04/30/2016|    0    |
|  01  |05/01/2016|05/31/2016|    0    |
|  01  |06/01/2016|06/30/2016|    0    |
|  01  |07/01/2016|07/31/2016|    0    |
|  01  |08/01/2016|08/31/2016|  1000   |
|  01  |09/01/2016|09/30/2016|    0    |
|  01  |10/01/2016|10/31/2016|    0    |
|  01  |11/01/2016|11/30/2016|    0    |
|  01  |12/01/2016|12/31/2016|    0    |

我在这里使用了另一个问题中的代码MySQL monthly Sale of last 12 months including months with no Sale

SELECT ID, 
IF( MONTH( E_From ) =  1, Total_PBR, 0 ) AS  'Jan', 
IF( MONTH( E_From ) =  2, Total_PBR, 0 ) AS  'Feb', 
IF( MONTH( E_From ) =  3, Total_PBR, 0 ) AS  'Mar', 
IF( MONTH( E_From ) =  4, Total_PBR, 0 ) AS  'Apr', 
IF( MONTH( E_From ) =  5, Total_PBR, 0 ) AS  'May', 
IF( MONTH( E_From ) =  6, Total_PBR, 0 ) AS  'Jun', 
IF( MONTH( E_From ) =  7, Total_PBR, 0 ) AS  'Jul', 
IF( MONTH( E_From ) =  8, Total_PBR, 0 ) AS  'Aug', 
IF( MONTH( E_From ) =  9, Total_PBR, 0 ) AS  'Sep', 
IF( MONTH( E_From ) =  10, Total_PBR, 0 ) AS 'Oct', 
IF( MONTH( E_From ) =  11, Total_PBR, 0 ) AS 'Nov', 
IF( MONTH( E_From ) =  12, Total_PBR, 0 ) AS 'Dec', year(E_FROM) AS Year,
SUM(Total_PBR ) AS total_yearly
FROM view_remit
GROUP BY PEN, YEAR( E_From )
LIMIT 0 , 30

它有效，但无法正确计算某些月份的total_PBR。任何帮助表示赞赏。

Answer 1

试试这个:

SELECT ID, 
SUM(IF( MONTH( E_From ) =  1, Total_PBR, 0 )) AS  'Jan', 
SUM(IF( MONTH( E_From ) =  2, Total_PBR, 0 )) AS  'Feb', 
SUM(IF( MONTH( E_From ) =  3, Total_PBR, 0 )) AS  'Mar', 
SUM(IF( MONTH( E_From ) =  4, Total_PBR, 0 )) AS  'Apr', 
SUM(IF( MONTH( E_From ) =  5, Total_PBR, 0 )) AS  'May', 
SUM(IF( MONTH( E_From ) =  6, Total_PBR, 0 )) AS  'Jun', 
SUM(IF( MONTH( E_From ) =  7, Total_PBR, 0 )) AS  'Jul', 
SUM(IF( MONTH( E_From ) =  8, Total_PBR, 0 )) AS  'Aug', 
SUM(IF( MONTH( E_From ) =  9, Total_PBR, 0 )) AS  'Sep', 
SUM(IF( MONTH( E_From ) =  10, Total_PBR, 0 )) AS 'Oct', 
SUM(IF( MONTH( E_From ) =  11, Total_PBR, 0 )) AS 'Nov', 
SUM(IF( MONTH( E_From ) =  12, Total_PBR, 0 )) AS 'Dec', 
year(E_FROM) AS Year,
FROM view_remit
GROUP BY ID, YEAR( E_From )

如果每个月只有一条数据记录，请忽略 SUM() 函数