题解 | #异常的邮件概率#
异常的邮件概率
http://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
依然详细解法
①找到排除掉黑名单之后的信息
1、找到非黑名单对应的用户
SELECT id FROM user WHERE black_list=0
2、找到排除掉黑名单之后的发送邮件的信息
SELECT E.`date`,E.type FROM email E WHERE E.semd_id IN ( SELECT id FROM user WHERE black_list=0 ) AND E.receivr_id IN ( SELECT id FROM user WHERE black_list=0)② 找到表中总计发送的次数和发送失败的次数
SELECT QBXXB.date，COUNT(*),COUNT(IF(QBXXB.TYPE='no_completed',1,null)) FROM (SELECT E.`date`,E.type FROM email E WHERE E.semd_id IN ( SELECT id FROM user WHERE black_list=0 ) AND E.receivr_id IN ( SELECT id FROM user WHERE black_list=0)) QBXXB GROUP BY QBXXB.date③在利用数值函数format 和round 取三位和第三位后四舍五入即可
查看8道真题和解析