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

/*
WITH a AS(
SELECT et.team_id, team_name, region, game_type, match_date, opponent, kills, deaths, match_id, 
    kills/deaths,
    CASE WHEN is_win = 1 THEN '胜' ELSE '负' END result,
    DENSE_RANK() OVER(PARTITION BY team_name ORDER BY match_date DESC) rk 
FROM match_records mr 
JOIN esports_teams et USING(team_id)
)
SELECT team_name, region, match_date, opponent, kills, deaths, 
    ROUND(kills/deaths,2) kda_ratio, result
FROM a
WHERE rk BETWEEN 1 AND 3
ORDER BY team_id, match_date DESC, match_id
*/
SELECT 
    et.team_name,
    et.region,
    mr.match_date,
    mr.opponent,
    mr.kills,
    mr.deaths,
    ROUND(mr.kills / mr.deaths, 2) AS kda_ratio,
    CASE WHEN mr.is_win = 1 THEN '胜' ELSE '负' END AS result
FROM esports_teams et
LEFT JOIN LATERAL (
    SELECT match_date, opponent, kills, deaths, is_win, match_id
    FROM match_records
    WHERE team_id = et.team_id
    ORDER BY match_date DESC, match_id ASC
    LIMIT 3
) mr ON TRUE
ORDER BY et.team_id ASC, mr.match_date DESC, mr.match_id ASC