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

我太菜了这题没做出来…… 分享一下搜集到四种很好的jietisi

写法1：sum case 计数神器

select distinct date,
    sum( case when (user_id, date) in (
        select user_id, min(date)  -- 得到每个用户的第一次登录id
        from login 
        group by user_id
    ) then 1 else 0 end )   
from login 
group by date

写法2：别人写的基于开窗函数：

select t.date,
sum(case when t.t_rank=1 then 1 else 0 end)
from (
    select user_id, date, row_number() over(partition by user_id order by date) t_rank  -- 给出每个用户的登录时间和次数
    from login
) as t
group by t.date

写法3：别人写的用left join + with的方法：

with new_user as
(
    select user_id,min(date) as mdate  --  用户  首次登录日期
    from login
    group by user_id
)

select  l.date, count(nu.user_id)
from login l
left join new_user nu
    on l.user_id=nu.user_id and l.date=nu.mdate   -- 好！！！！
group by l.date
order by l.date

写法4：

先得到所有日期表;

left join 用户首次登录日期

select a.date,   count(b.user_id)
from (
  select distinct date  -- 得到所有日期
  from login 
) a
left join (
  select user_id, min(date) as date
  from login 
  group by user_id
)b on a.date=b.date
group by a.date
order by a.date

```