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

题目要求用lateral join，找ai学习了一下，看起来比窗口函数排名要高级一些

select
e.team_name,
e.region,
m.match_date,
m.opponent,
m.kills,
m.deaths,
m.kda_ratio,
m.result
from esports_teams e
left join lateral (
    select mr.match_id,
           mr.match_date,
           mr.opponent,
           mr.kills, 
           mr.deaths,
           round(mr.kills/mr.deaths,2) kda_ratio,
           case when mr.is_win = 1 then '胜'
                when mr.is_win = 0 then '负'
            end result
    from match_records mr
    where e.team_id = mr.team_id
    order by mr.match_date desc
    limit 3
) m on true
order by e.team_id, m.match_date desc, m.match_id