题解 | 电竞赛事战队近期战绩查询

select e.team_name,e.region,b.match_date,b.opponent,b.kills,b.deaths,b.kda_ratio,b.result
from
esports_teams e join lateral
(
select match_date,opponent,kills,deaths,kda_ratio,result
from(
select match_date,opponent,kills,deaths,round(kills/deaths,2) as kda_ratio,
case
when
is_win =1 then '胜' else '负' end as result,
row_number() over (partition by team_id order by team_id,match_date desc,match_id) as rk
from
match_records m
where m.team_id = e.team_id
) a 
where rk <=3
) b on true