题解 | #异常的邮件概率#

方法一：子查询



# # 正常用户的id： 1，3，4    不正常用户的id为  2 ；则排除不正常用户的id
# send_id not in (select id from user where is_blacklist=1)
# and
# receive_id not in  (select id from user where is_blacklist=1)
# # 让你统计正常用户发送给正常用户邮件失败的概率    ： 正常用户：发送失败的/发送总数
# round(sum(if(type="no_completed",1,0))/count(type),3)
# # 最后SQL语句为
select date ,round(sum(if(type="no_completed",1,0))/count(type),3) as p from email
where send_id not in (select id from user where is_blacklist=1)
and
receive_id not in  (select id from user where is_blacklist=1)
group by date
order by date;


方法二：评论区的连表查询