第一百题留念

题目
表: UserActivity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| username      | varchar |
| activity      | varchar |
| startDate     | Date    |
| endDate       | Date    |
+---------------+---------+

该表不包含主键
该表包含每个用户在一段时间内进行的活动的信息
名为 username 的用户在 startDate 到 endDate 日内有一次活动

写一条SQL查询展示每一位用户 最近第二次 的活动

如果用户仅有一次活动，返回该活动

一个用户不能同时进行超过一项活动，以 任意 顺序返回结果

下面是查询结果格式的例子：

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  |
+------------+--------------+-------------+-------------+

Result 表:

+------------+--------------+-------------+-------------+
| 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 只有一条记录，我们就取这条记录

解答
统计每个用户的活动次数

selet U.username, count(*) as count
from UserActivity as U
group by U.username

如果出现一次则返回本身

selet U.username, U.activity, U.startDate, U.endDate
from UserActivity as U
group by U.username
having count(*) = 1

对于startDate升序给定不同user的rank
取出 rank为2的即可

select U.username, U.activity, U.startDate, U.endDate,
@rank:=if(U.username = @pre_username, @rank +1, 1) as rank,
@pre_username:= U.username 
from UserActivity as U, (select @rank:=0, @pre_username:=NULL) as init 
order by U.startDate;

select tmp.username, tmp.activity, tmp.startDate, tmp.endDate
from (select U.username, U.activity, U.startDate, U.endDate,
@rank:=if(U.username = @pre_username, @rank +1, 1) as rank,
@pre_username:= U.username 
from UserActivity as U, (select @rank:=0, @pre_username:=NULL) as init 
order by U.startDate) as tmp
where tmp.rank = 2;

把两个结果union即可

selet U.username, U.activity, U.startDate, U.endDate
from UserActivity as U
group by U.username
having count(*) = 1

union

select tmp.username, tmp.activity, tmp.startDate, tmp.endDate
from (select U.username, U.activity, U.startDate, U.endDate,
@rank:=if(U.username = @pre_username, @rank +1, 1) as rank,
@pre_username:= U.username 
from UserActivity as U, (select @rank:=0, @pre_username:=NULL) as init 
order by U.startDate) as tmp
where tmp.rank = 2;

别的方法

select USERNAME, ACTIVITY, STARTDATE, ENDDATE from UserActivity group by username having count(*) = 1
 
union
 
select u2.username, u2.activity, u2.startdate,u2.enddate
from UserActivity as u1, UserActivity as u2 where u1.username = u2.username
group by u2.username, u2.enddate
having sum(case when u2.enddate<u1.enddate then 1 else 0 end) =1