题解 | #在audit表上创建外键约束，其emp_no对应employees_test表的主键id#

select b.date,ifnull(a.p,0) p from

(SELECT l1.date,round(sum(case when l2.user_id is null then 0 else 1 end)/count(*),3) p from login l1 left join login l2 on l1.user_id=l2.user_id and DATE_ADD(l1.date,INTERVAL 1 day)=l2.date where (l1.user_id,l1.date) in (select user_id,min(date) from login group by user_id) group by l1.date) a

right join

(select date from login group by date) b

on a.date=b.date