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

WITH t AS (
    SELECT e.team_id,
           match_id,
           team_name,
           region,
           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 match_date DESC) AS rnk
    FROM esports_teams e
    LEFT JOIN match_records m ON e.team_id=m.team_id
)
SELECT team_name,
           region,
           match_date,
           opponent,
           kills,
           deaths,
           kda_ratio,
           result
FROM t 
WHERE rnk<=3
ORDER BY team_id,match_date DESC,match_id ASC