mysql - 即使数据不存在或在另一个月存在，如何获取所有 7 天的结果

Question

选择查询所有 7 天的结果，即使数据不存在或在另一个月份存在。

我有两张 table 。第一个是 jobs，另一个是 dashed_months这是职位表:

'id', 'job_amount', 'created_at'
'1', '50.0', '2018-04-16 11:01:36'
'2', '160', '2018-05-17 10:31:02'
'3', NULL, '2018-04-17 10:36:28'
'4', NULL, '2018-04-17 10:36:50'
'5', NULL, '2018-04-17 10:37:00'
'6', NULL, '2018-04-17 15:21:45'
'7', NULL, '2018-04-17 15:23:10'
'8', NULL, '2018-04-17 15:25:18'
'10', NULL, '2018-04-17 15:32:49'

和月份表:

'id', 'month_name', 'm_type'
'1', 'January', '1'
'2', 'February', '1'
'3', 'March', '1'
'4', 'April', '1'
'5', 'May', '1'
'6', 'June', '1'
'7', 'July', '1'
'8', 'August', '1'
'9', 'September', '1'
'10', 'October', '1'
'11', 'November', '1'
'12', 'December', '1'
'13', 'Monday', '2'
'14', 'Tuesday', '2'
'15', 'Wednesday', '2'
'16', 'Thursday', '2'
'17', 'Friday', '2'
'18', 'Saturday', '2'
'19', 'Sunday', '2'

我正在使用此查询来获取month_name 和job_amount 总和。查询:

select
    dashboard_months.month_name,
    IFNULL(SUM(job_amount), 0) AS revenu
from
    dashboard_months
        LEFT OUTER JOIN
    jobs ON dayname(jobs.created_at) = dashboard_months.month_name
where
    (dashboard_months.m_type = 2)
        AND (MONTH(jobs.created_at) = 4
        OR MONTH(jobs.created_at) IS NULL)
        AND (YEAR(jobs.created_at) = 2018
        OR YEAR(jobs.created_at) IS NULL)
group by dashboard_months.month_name
order by dashboard_months.id;

和输出:

'Monday', '50'
'Tuesday', '0'
'Wednesday', '0'
'Friday', '0'
'Saturday', '0'
'Sunday', '0'

和所需的输出:

'Monday', '50'
'Tuesday', '0'
'Wednesday', '0'
'Thursday', '0'
'Friday', '0'
'Saturday', '0'
'Sunday', '0'

您可以看到缺少星期四，因为它出现在第 5 个月。

'2', '160', '2018-05-17 10:31:02'

如何才能每周都有星期四？

Answer 1

select
   dashboard_months.month_name,
    IFNULL(SUM(job_amount), 0) AS revenu
from
    dashboard_months
LEFT OUTER JOIN jobs 
ON dayname(jobs.created_at) = dashboard_months.month_name 
AND (MONTH(jobs.created_at) = 4
     OR MONTH(jobs.created_at) IS NULL) 
AND (YEAR(jobs.created_at) = 2018
        OR YEAR(jobs.created_at) IS NULL)
where dashboard_months.m_type = '2'
group by dashboard_months.month_name
order by dashboard_months.id;

输出

month_name  revenu
Monday      50
Tuesday     0
Wednesday   0
Thursday    0
Friday      0
Saturday    0
Sunday      0

演示

http://sqlfiddle.com/#!9/572b5b/20