mysql - 缺月补零

Question

我试图在缺少的月​​份显示零，但没有成功。

表:

clicks | impressions | ctr | position | month | year

  111       2709            4      20          3         2015
  101       2695            3      20          6         2015
  76        2714            2      21          7         2015
  .
  .
  .
  64        1212            4      25          11        2015
  81        1905            4      24          12        2015

要求的输出:

clicks | impressions | ctr | position | month | year

   0          0             0       0           1       2015
   0          0             0       0           2       2015
  111       2709            4       20          3       2015
   0          0             0       0           4       2015
   0          0             0       0           5       2015
  101       2695            3       20          6       2015
   .
   .
   .
   64       1212            4       25          11      2015
   81       1905            4       24          12      2015

Answer 1

就像@jarlh 建议的那样，您可以这样做:创建一个包含所有可用月份的“表”(您必须自己填充它，添加您想要的月份和年份)，然后 left join to the original表，当值不存在时，置 0。

select coalese(s.clicks,0) as clicks,
       coalese(s.impressions ,0) as impressions,
       coalese(s.ctr ,0) as ctr ,
       coalese(s.position ,0) as position ,
       t.month,
       t.year
from(
    SELECT 1 as month_num,2015 as year_num
    union SELECT 2,2015
    union select 3,2015
    union select 4,2015 ....) t
LEFT OUTER JOIN YourTable s
ON(t.month_num = s.month and t.year_num = s.year)