mysql - 从 mysql 计数返回零，即使该月没有结果

Question

我有一个日志表，我想在图表中显示详细信息，所以我想要一个查询来显示从一月到当月的所有记录，如果那个月的事件为零，它应该返回零以便图表可以流动，

可能表格如下所示:

id      user_id  action                 date_added
75      56      Base Adding            2017-02-29 11:52:35
76      56      Base Adding            2017-02-29 11:59:28
77      61      Fuel Adding            2017-02-29 12:05:16
78      56      Update details         2017-02-29 12:17:08
79      57      Logged In              2017-02-29 12:25:41
80      56      Base Adding            2017-03-29 12:40:56
81      55      Logged In              2017-02-29 12:51:31
82      56      Base Adding            2017-05-29 12:53:25

我最想看到的结果是这样的:

MonthAdded         Totalcount
2017-01              0
2017-02              2
2017-03              1
2017-04              0
2017-05              1

我的查询看起来像这样，它没有给我输出，请帮助我。

SELECT DATE_FORMAT(date_added, '%Y-%m') AS MonthAdded, COUNT(id) AS Totalcost FROM audit_log WHERE date_added LIKE '%2017%' AND user_id = '56' AND (action = 'Base Adding') GROUP BY DATE_FORMAT(date_added, '%Y-%m') ASC

Answer 1

根据您的示例数据，这是详细信息:

详细结果:

select  MonthAdded,count(date_substr) as cnt   from        
(select '2017-01'as MonthAdded  UNION all
select '2017-02' UNION all
select '2017-03'  UNION all
select '2017-04'  UNION all
select '2017-05' UNION all
select '2017-06'  UNION all
select '2017-07'  UNION all
select '2017-08'  UNION all
select '2017-09'  UNION all 
select '2017-10'  UNION all 
select '2017-11'  UNION all 
select '2017-12') as a

left join

(select id, user_id, action, date_added,SUBSTRING(date_added FROM 1 FOR 7) as date_substr from
(select 75 as id,      56 as user_id,     'Base Adding' action,            '2017-02-29 11:52:35' as date_added UNION all
select  76,      56,      'Base Adding',            '2017-02-29 11:59:28' UNION all
select 77,      61,      'Fuel Adding',            '2017-02-29 12:05:16' UNION all
select 78,      56,      'Update details' ,        '2017-02-29 12:17:08' UNION all
select 79,      57,      'Logged In' ,            '2017-02-29 12:25:41' UNION all
select 80,      56,      'Base Adding',           '2017-03-29 12:40:56' UNION all
select 81,      55,      'Logged In' ,            '2017-02-29 12:51:31' UNION all
select  82,      56,      'Base Adding',            '2017-05-29 12:53:25') as a
where action = 'Base Adding' and user_id = 56) as b

on a.MonthAdded = b.date_substr
group by MonthAdded

最终查询结构:

select  MonthAdded,count(date_substr) as cnt   from        
(select '2017-01'as MonthAdded  UNION all
select '2017-02' UNION all
select '2017-03'  UNION all
select '2017-04'  UNION all
select '2017-05' UNION all
select '2017-06'  UNION all
select '2017-07'  UNION all
select '2017-08'  UNION all
select '2017-09'  UNION all 
select '2017-10'  UNION all 
select '2017-11'  UNION all 
select '2017-12') as a

left join

(select id, user_id, action, date_added,SUBSTRING(date_added FROM 1 FOR 7) as date_substr from
(select * from yourtable) as a
where action = 'Base Adding' and user_id = 56) as b

on a.MonthAdded = b.date_substr
group by MonthAdded

它包括直到 12 月，以确保您是否有数据。

结果:

2017-01 0
2017-02 2
2017-03 1
2017-04 0
2017-05 1
2017-06 0
2017-07 0
2017-08 0
2017-09 0
2017-10 0
2017-11 0
2017-12 0