题解 | #牛客每个人最近的登录日期(四)#
牛客每个人最近的登录日期(四)
https://www.nowcoder.com/practice/e524dc7450234395aa21c75303a42b0a?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1&sourceUrl=%2Fexam%2Foj%3Fpage%3D2%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82
select date,count(t1.md) from login left join
(select user_id,min(date)md from login group by user_id)t1
on t1.user_id=login.user_id and t1.md=login.date
group by date
order by date
其他方法1:
select date
,count(distinct case when (user_id,date) in
(select user_id,min(date)from login group by user_id)
then user_id else null end)
from login
group by date
order by date;
其他方法2:
select a.date, count(b.user_id) new
from (select distinct date from login) a
left join (select user_id, min(date) first_date from login group by user_id)
b on a.date=b.first_date
group by a.date
order by a.date
SQL题解 文章被收录于专栏
主要是为自己做个笔记