【SQL】1890. 2020年最后一次登录（简单写法；窗口函数写法）

前述

sql 中 between 的边界问题 ---- between 边界：闭区间，not between 边界：开区间

在 sql 中，

• between 边界：闭区间
• not between 边界：开区间

题目描述

leetcode题目：1890. 2020年最后一次登录

Code

写法一：简单写法

过滤2020年后，再选最大值。

select user_id, max(time_stamp) as last_stamp
from Logins
where year(time_stamp) = '2020'
group by user_id

写法二：窗口函数

select user_id, time_stamp as last_stamp
from(
    select *, 
        row_number() over(partition by user_id order by time_stamp desc) as rk
    from Logins
    where time_stamp between '2020-01-01 00:00:00' and '2020-12-31 23:59:59'
    -- where year(time_stamp) = '2020'
) A
where rk = 1

where time_stamp between '2020-01-01 00:00:00' and '2020-12-31 23:59:59' 和
where year(time_stamp) = '2020' 两种写法都可以。
注意：
在 sql 中，

• between 边界：闭区间
• not between 边界：开区间