如何创建包含最近3次历史得分列的SQL记录集？

完全可以，利用**窗口函数LAG()**就能简洁地实现这个需求，这是标准SQL语法，支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）都能运行。

具体查询语句 #

SELECT
    MatchDate,
    PlayerID,
    Score,
    LAG(Score, 1) OVER (PARTITION BY PlayerID ORDER BY MatchDate ASC) AS PreviousScore_1,
    LAG(Score, 2) OVER (PARTITION BY PlayerID ORDER BY MatchDate ASC) AS PreviousScore_2,
    LAG(Score, 3) OVER (PARTITION BY PlayerID ORDER BY MatchDate ASC) AS PreviousScore_3
FROM Results
WHERE PlayerID = 2
ORDER BY MatchDate DESC;

语句说明 #

• LAG(Score, n)：用于获取当前行之前第n行的Score值，如果没有对应行则返回NULL，正好符合你需要的空值逻辑
• PARTITION BY PlayerID：限定仅在同一球员的记录范围内计算历史得分，避免不同球员的记录互相干扰
• ORDER BY MatchDate ASC：按比赛日期升序排序，确保LAG(1)取的是当前比赛的**上一场（更早的最近一场）**得分，LAG(2)是上两场，以此类推
• 最后ORDER BY MatchDate DESC：让结果按最新比赛在前展示，和你提供的示例格式完全匹配

针对老版本数据库（如MySQL 5.x）的替代方案 #

如果你的数据库不支持窗口函数，也可以通过自连接实现单条查询，但效率不如窗口函数：

SELECT
    r1.MatchDate,
    r1.PlayerID,
    r1.Score,
    r2.Score AS PreviousScore_1,
    r3.Score AS PreviousScore_2,
    r4.Score AS PreviousScore_3
FROM Results r1
LEFT JOIN Results r2 
    ON r1.PlayerID = r2.PlayerID 
    AND r2.MatchDate = (SELECT MAX(MatchDate) FROM Results WHERE PlayerID = r1.PlayerID AND MatchDate < r1.MatchDate)
LEFT JOIN Results r3 
    ON r1.PlayerID = r3.PlayerID 
    AND r3.MatchDate = (SELECT MAX(MatchDate) FROM Results WHERE PlayerID = r1.PlayerID AND MatchDate < r2.MatchDate)
LEFT JOIN Results r4 
    ON r1.PlayerID = r4.PlayerID 
    AND r4.MatchDate = (SELECT MAX(MatchDate) FROM Results WHERE PlayerID = r1.PlayerID AND MatchDate < r3.MatchDate)
WHERE r1.PlayerID = 2
ORDER BY r1.MatchDate DESC;

内容的提问来源于stack exchange，提问作者Peter Rundqvist