Mysql按日期统计行数据，但数据日期很少

Question

我有一个这样的表"activity“

idEmployee  | activity | Date
1           | a        | 2019/01/01
1           | b        | 2019/01/01
2           | c        | 2019/01/01
2           | d        | 2019/01/01
1           | e        | 2019/01/02
2           | f        | 2019/01/03
1           | f        | 2019/01/03
3           | c        | 2019/01/01
4           | d        | 2019/01/03
1           | e        | 2019/01/02
2           | f        | 2019/01/03

我想像这样计算2019/01/01 - 2019/01/03没有活动的每个idEmpolyee (作为total_no_actitivity)的每个日期

idEmployee | total_no_activity
1          | 0
2          | 1 (2019/01/02
3          | 2 (2019/01/02,2019/01/03)
4          | 2 (2019/01/01,2019/01/02)

但我只能选择没有活动的idemployee，不能计算total_no_activity。

SELECT idEmployee, namaLengkap, date
FROM account LEFT JOIN timesheet USING (idEmployee)
WHERE NOT EXISTS (SELECT idEmployee
                  FROM timesheet
                  WHERE account.idEmployee = timesheet.idEmployee AND weekday(date) AND date                    between '2019/08/05' and '2019/08/09' AND idrole = '4' AND statusaktif = '1' )
ORDER BY idEmployee ASC

是否可以仅使用表"activity“来计算total_no_activity？

Answer 1

SELECT idEmployee, 
       3 - COUNT(DISTINCT `Date`) total_no_activity
FROM account
WHERE `Date` BETWEEN `2019/01/01` AND `2019/01/03`
GROUP BY idEmployee

其中3是期间内的天数(含利息)。

如果某些idEmployee在感兴趣的时间段内根本没有记录，那么这个值将不会在输出中列出。

很遗憾，我需要在输出中列出没有记录的idEmployee

假设您需要源表中出现的所有idEmployee值至少一次(甚至在感兴趣的时间段之外)使用

SELECT account.idEmployee, 
       3 - COUNT(DISTINCT account.`Date`) total_no_activity
FROM (SELECT DISTINCT idEmployee FROM account) all_employees
LEFT JOIN account USING (idEmployee)
WHERE account.`Date` BETWEEN `2019/01/01` AND `2019/01/03`
GROUP BY account.idEmployee

Answer 2

我建议：

select a.idEmployee,
       (datediff(params.date2, params.date1) + 1 - 
        count(distinct ac.date)
       ) as missing_days
from (select date('2019-01-01') as date1, date('2019-01-03') as date2
     ) params cross join   -- a convenience so we don't have to retype the constants
     accounts a left join
     activity ac
     on ac.idEmployee = a.idEmployee and
        ac.date >= params.date1 and
        ac.date <= params.date2
group by a.idEmployee;

为了防止输入错误并允许日期容易更改，这引入了一个具有日期值的子查询params。