题解 | #牛客每个人最近的登录日期(三)#
牛客每个人最近的登录日期(三)
http://www.nowcoder.com/practice/16d41af206cd4066a06a3a0aa585ad3d
依然详细版（个人理解，比较繁琐）
①先找到每个客户的首次登录时间
SELECT L.user_id,MIN(L.date) FROM login L GROUP BY L.user_id② 利用函数找到对应客户客户的下次登陆时间
SELECT CCDLB.*,DATE_ADD(CCDLB.CCDLSJ,INTERVAL 1 DAY) AS XCDLSJ FROM (SELECT L.user_id,MIN(L.date) AS CCDLSJ FROM login L GROUP BY L.user_id) AS CCDLB;③连接login表找到对应的上的客户
SELECT QBXXB.*,L1.date FROM (SELECT CCDLB.*,DATE_ADD(CCDLB.CCDLSJ,INTERVAL 1 DAY) AS XCDLSJ FROM (SELECT L.user_id,MIN(L.date) AS CCDLSJ FROM login L GROUP BY L.user_id) AS CCDLB) AS QBXXB LEFT JOIN login L1 ON QBXXB.user_id=L1.user_id AND QBXXB.XCDLSJ=L1.date④分别计数
SELECT COUNT(*) AS QBKH,COUNT(IF(QBB.date IS null,null,1)) AS LCKH FROM (SELECT QBXXB.*,L1.date FROM (SELECT CCDLB.*,DATE_ADD(CCDLB.CCDLSJ,INTERVAL 1 DAY) AS XCDLSJ FROM (SELECT L.user_id,MIN(L.date) AS CCDLSJ FROM login L GROUP BY L.user_id) AS CCDLB) AS QBXXB LEFT JOIN login L1 ON QBXXB.user_id=L1.user_id AND QBXXB.XCDLSJ=L1.date) AS QBB⑤计算概率
SELECT FORMAT(ROUND(B.LCKH/B.QBKH,4),3) AS P FROM (SELECT COUNT(*) AS QBKH,COUNT(IF(QBB.date IS null,null,1)) AS LCKH FROM (SELECT QBXXB.*,L1.date FROM (SELECT CCDLB.*,DATE_ADD(CCDLB.CCDLSJ,INTERVAL 1 DAY) AS XCDLSJ FROM (SELECT L.user_id,MIN(L.date) AS CCDLSJ FROM login L GROUP BY L.user_id) AS CCDLB) AS QBXXB LEFT JOIN login L1 ON QBXXB.user_id=L1.user_id AND QBXXB.XCDLSJ=L1.date) AS QBB) B看起来很复杂，其实后一步都是用的前一步的查询结果，望有助！
查看8道真题和解析