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

1、使用sum(case(where in))

select date,
sum(case when (user_id,date) in
(select user_id,min(date) from login group by user_id)
then 1 else 0 end) as new
from login
group by date
order by date

2、使用窗口函数

SELECT date,SUM(IF(line = 1, 1, 0))
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS line
      FROM login) a
GROUP BY date
ORDER BY date

3、笨办法

select distinct date,ifnull(new,0) from login l left join (select a.mdate,count(a.mdate) new from
(select min(date)  as mdate from login
group by user_id) a
group by a.mdate) b
on l.date=b.mdate

4、考虑某天user重复登录的情况

select b.date,
sum(case when b.date=a.mindate then 1 else 0 end) as new
from (select distinct * from login) b left join 
(select user_id,min(date) mindate from login group by user_id) a
on b.user_id=a.user_id
group by b.date