mysql - 如何获取每天的进出时间并在日期范围之间的查询中对其进行计数？

Question

这是我现在正在使用的查询:

Select *
from attendance
where (in_time BETWEEN '2019-06-20 00:00:01' and '2019-06-20 23:59:59')
and (out_time BETWEEN '2019-06-28 00:00:01' and '2019-06-28 23:59:59')

我需要这样的输出。

或者我如何根据示例数据库计算每个日期范围的任何想法

Answer 1

我认为您需要从日期列表开始，然后使用 left join 或相关子查询:

select d.dte,
       (select count(*)
        from attendance a
        where a.in_time <= dte and a.out_time >= dte
       ) as cnt
from (select date('2019-06-20') as dte union all
      select date('2019-06-21') as dte union all
      select date('2019-06-22') as dte union all
      select date('2019-06-23') as dte union all
      select date('2019-06-24') as dte union all
      select date('2019-06-25') as dte union all
      select date('2019-06-26') as dte union all
      select date('2019-06-27') as dte union all
      select date('2019-06-28') as dte
     ) d;