php - 统计一个数据表并求和，用默认值填充一个空数据

Question

我有一个只包含日期数据的表，我想统计数据在同一个月的次数，对于那个问题它已经完成了，现在我遇到了一个新问题，就是缺少月份，这是因为那个月没有数据。现在我想添加默认值为 0 的空月份，即使该月份不在表中。有人可以根据我的查询帮助我吗？

这是我的数据

start_date  |end_date
------------------------
 2018-10-01 |2018-10-02
 2018-01-04 |2018-02-04
 2018-08-01 |2018-10-01

这是我的查询

    select month(month_table) as month_table
         , sum(cstart) as cstart 
         , sum(cend) as cend 
      from 
         (
          (select `start_date` as month_table
                , 1 as cstart
                , 0 as cend 
             from newdata
          ) 
  union all 
          ( select `end_date`
                 , 0
                 , 1 
              from newdata 
           ) 
          ) dd 
      group 
         by monthname(month_table)
          , month(month_table) 
      order 
         by month(month_table)

结果是

month_table|cstart|cend
      1      |  1   |  0
      2      |  0   |  1
      8      |  1   |  0
      10     |  1   |  2

我想添加新的查询，这样我的结果就是

 month_table|cstart|cend
      1      |  1   |  0
      2      |  0   |  1
      3      |  0   |  0
      4      |  0   |  0
      5      |  0   |  0
      6      |  0   |  0
      7      |  0   |  0
      8      |  1   |  0
      9      |  0   |  0
      10     |  1   |  2
      11     |  0   |  0
      12     |  0   |  0

这是我的 fiddle http://sqlfiddle.com/#!9/c18b5f/3/0

Answer 1

您需要一张包含所有月份的表格。您可以使用 UNION ALL 创建一个带有子查询的临时查询。然后将计数子查询左连接到该表。

SELECT m.month month_table,
       coalesce(s.count, 0) cstart,
       coalesce(e.count, 0) cend
       FROM (SELECT 1 month
             UNION ALL
             SELECT 2 month
             UNION ALL
             SELECT 3 month
             UNION ALL
             SELECT 4 month
             UNION ALL
             SELECT 5 month
             UNION ALL
             SELECT 6 month
             UNION ALL
             SELECT 7 month
             UNION ALL
             SELECT 8 month
             UNION ALL
             SELECT 9 month
             UNION ALL
             SELECT 10 month
             UNION ALL
             SELECT 11 month
             UNION ALL
             SELECT 12 month) m
            LEFT JOIN (SELECT month(n.start_date) month,
                              count(*) count
                              FROM newdata n
                              GROUP BY month(n.start_date)) s
                      ON s.month = m.month
            LEFT JOIN (SELECT month(n.end_date) month,
                              count(*) count
                              FROM newdata n
                              GROUP BY month(n.end_date)) e
                      ON e.month = m.month
       ORDER BY m.month;