两次连表,而且连的都是同一张表,我最开始想的是 where 直接 not in 黑名单,但是还是之前那个问题,选择出的不一定与其他数据保持在同一行,所以必须要连表,round 函数进行除法运算,后面写 3 就是保留三位小数,,其他的就没有什么知识点了
select date, round(sum(type = 'no_completed') / count(*), 3) as p
from email e
inner join user u1 on e.send_id = u1.id
inner join user u2 on e.receive_id = u2.id
where u2.is_blacklist = 0 and u1.is_blacklist = 0
group by date
order by date