以力抠 550. 游戏玩法分析 IV:. - 力扣(LeetCode)为例子,记录SQL的with和日期的用法
with t1 as(
select player_id,min(event_date) first_date
from Activity
group by player_id
),
t2 as(
select player_id,first_date,DATE_ADD(first_date, INTERVAL 1 DAY) second_date,(select(second_date) - first_date) diff
from t1
),
t3 as (
select t2.player_id
from t2
where exists(
select player_id
from Activity act
where act.player_id = t2.player_id and
act.event_date = t2.second_date
)
)
select round((select count(*) from t3) /(select count(distinct (player_id)) from Activity) ,2)fraction
要点:
1、with的中间表可以引用前面建好的中间表,如t2引用t1,t3引用t2(这些中间表就是你的问题处理过程)。
2、日期相加函数DATE_ADD的用法:DATE_ADD(some_date, INTERVAL n DAY)。