SQL架构
表: UserActivity
+---------------+---------+ | Column Name | Type | +---------------+---------+ | username | varchar | | activity | varchar | | startDate | Date | | endDate | Date | +---------------+---------+ 该表不包含主键 该表包含每个用户在一段时间内进行的活动的信息 名为 username 的用户在 startDate 到 endDate 日内有一次活动
写一条SQL查询展示每一位用户 最近第二次 的活动
如果用户仅有一次活动,返回该活动
一个用户不能同时进行超过一项活动,以 任意 顺序返回结果
下面是查询结果格式的例子。
示例 1:
输入:
UserActivity
表:
+------------+--------------+-------------+-------------+
| username | activity | startDate | endDate |
+------------+--------------+-------------+-------------+
| Alice | Travel | 2020-02-12 | 2020-02-20 |
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Alice | Travel | 2020-02-24 | 2020-02-28 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
+------------+--------------+-------------+-------------+
输出:
+------------+--------------+-------------+-------------+
| username | activity | startDate | endDate |
+------------+--------------+-------------+-------------+
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
+------------+--------------+-------------+-------------+
解释:
Alice 最近一次的活动是从 2020-02-24 到 2020-02-28 的旅行, 在此之前的 2020-02-21 到 2020-02-23 她进行了舞蹈
Bob 只有一条记录,我们就取这条记录
with e as (select
username,activity, row_number() over(partition by username order by startDate desc) r,startDate,endDate
from
UserActivity)
select username,activity,startDate,endDate from e where r =2
union all
select username,activity,startDate,endDate from e where r = 1 and username not in (select username from e where r =2)
with e as (select
username,activity, row_number() over(partition by username order by startDate desc) r,startDate,endDate
from
UserActivity) # 封装成 虚拟表 减少 重复代码的书写
select username,activity,startDate,endDate from e where r =2 # 选出 第二次 的活动
union all
select username,activity,startDate,endDate from e where r = 1 and username not in (select username from e where r =2)
# 选出 只有 一次 的 活动
开两个窗:
SELECT u1.username,u1.activity,u1.startDate,u1.endDate
from(
SELECT *,
rank() over(partition by username order by startDate desc ) as 'rank1',
COUNT(*)over(partition by username ) as 'count1'
FROM UserActivity) as u1
WHERE rank1 = 2
or count1 =1