题解 | #牛客每个人最近的登录日期(四)#

1.解法一

首先通过子查询选出每一个user的user_id和最早登陆时间，并命名为temp。

(select user_id, min(date) as min_date from login
 group by user_id) as temp

然后用left join连接原表login和temp，用temp.user_id = l.user_id and temp.min_date = l.date来对齐，这样原表login中的数据全部保留，temp中缺失的部分留空，然后根据login中的date来分组汇总。

login as l left join
(select user_id, min(date) as min_date from login
 group by user_id) as temp
on temp.user_id = l.user_id and temp.min_date = l.date
group by l.date
order by l.date

然后从join之后的表中选出l.date和新用户的数量。这里用if判断是否存在最早登陆日期为当前日期的用户，如果有则赋值为当前日期user_id的数量，如不存在则为0.

select l.date, 
if(exists(select * from 
          (select user_id, min(date) as min_date from login
           group by user_id) as temp2 where temp2.min_date = l.date), 
          count(temp.user_id), 0) 

全部整合在一起就是以下最终答案。

select l.date, 
if(exists(select * from 
          (select user_id, min(date) as min_date from login
           group by user_id) as temp2 where temp2.min_date = l.date), 
          count(temp.user_id), 0) 
FROM
login as l left join
(select user_id, min(date) as min_date from login
 group by user_id) as temp
on temp.user_id = l.user_id and temp.min_date = l.date
group by l.date
order by l.date

2.解法二

首先通过子查询选出每一个user的user_id和最早登陆时间，并命名为temp。然后从再根据最早登陆时间分组汇总并用count()计算出新用户数量，并命名为temp2.

(select temp.min_date, count(temp.user_id) as new from
(select user_id, min(date) as min_date from login
 group by user_id) as temp
 group by temp.min_date) as temp2

然后用left join连接原表login和temp，用temp.min_date = l.date来对齐，然后根据login中的date来分组汇总，最后从join得出的表中选出l.date和新用户的数量。这里用if判断l.date当前日期是否在temp2统计新用户数量时被统计到，如果没有则说明当天不存在新用户，赋值为0，反之则赋值为新用户数量new。

select l.date, 
if(l.date in (select min(date) as min_date from login
 group by user_id), new, 0)

全部整合在一起就是以下最终答案。

select l.date, 
if(l.date in (select min(date) as min_date from login
 group by user_id), new, 0)
from login as l
left join
(select temp.min_date, count(temp.user_id) as new from
(select user_id, min(date) as min_date from login
 group by user_id) as temp
 group by temp.min_date) as temp2
on l.date = temp2.min_date
group by l.date
order by l.date