Mysql按工作日分组，填写缺少的工作日 [英] Mysql group by weekday, fill in missing weekdays

问题描述

我在使用MySQL查询时遇到问题，该查询按工作日将表中的数据分组.

I have a problem with a MySQL query, that groups data in my table by weekday.

我需要它来填充数据中缺少的工作日，例如下面的SQL示例中的星期日(工作日7).

I need it to fill in weekdays that a missing in the data, for example sunday (weekday 7) in the SQL-example below.

SQL小提琴

MySQL 5.6模式设置:

create table test (
`id` INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`date` DATETIME
);

INSERT INTO test (`date`) VALUES 
('2016-05-16 00:00:00'),
('2016-05-17 00:00:00'),
('2016-05-18 00:00:00'),
('2016-05-20 00:00:00'),
('2016-05-21 00:00:00'),
('2016-05-22 00:00:00'),
('2016-05-16 00:00:00'),
('2016-05-17 00:00:00'),
('2016-05-18 00:00:00'),
('2016-05-20 00:00:00');

查询1 :

SELECT WEEKDAY(date) AS weekday,
    COUNT(id) AS posts
FROM test
GROUP BY WEEKDAY(date)

结果 :

Results:

| weekday | posts |
|---------|-------|
|       0 |     2 |
|       1 |     2 |
|       2 |     2 |
|       4 |     2 |
|       5 |     1 |
|       6 |     1 |

我希望它也会返回此行.

I expect it return this line too.

|       3 |     0 |

我的完整查询非常复杂，希望我能找到一个快速的解决方案.

My complete query i quite complex so i hope you find a quick solution for this.

推荐答案

通常的方法是left join:

select wd.wd, count(t.id)
from (select 1 as wd union all select 2 union all select 3 union all select 4 uion all
      select 5 union all select 6 union all select 7
     ) wd left join
     test t
     on wd.wd = weekday(t.date)
group by wd.wd
order by wd.wd;

这篇关于Mysql按工作日分组，填写缺少的工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！