sql - 仅选择最后一次尝试

Question

考虑以下名为 score 的假想表的表结构:

player_name   |player_lastname   |try   |score   
 primary key: (player_name,player_lastname,try)

(不讨论表模式，它只是一个例子)

此表包含所有玩家的分数 - 每个玩家都应该能够玩一次或两次。现在，我如何才能仅获取有关每个玩家最后一次尝试的数据(即，对于玩了不止一次的玩家，应该忽略第一次尝试)？

我正在努力实现的一个例子:

player_name,player_lastname,try,score
=====================================
bart, simpson,1,250
lisa,simpson,1,150
lisa,simpson,2,250
homer,simpson,1,300
homer,simpson,2,350
maggi,simpson,1,50

结果应该是:

player_name,player_lastname,try,score
=====================================
bart, simpson,1,250
lisa,simpson,2,250
homer,simpson,2,350
maggi,simpson,1,50

Answer 1

一种选择是使用带有 MAX 的子查询将表JOIN 自身:

select s.*
from score s
  join (
    select max(try) maxtry, player_name, player_lastname
    from score
    group by player_name, player_lastname
    ) s2 on s.player_name = s2.player_name
      and s.player_lastname = s2.player_lastname
      and s.try = s2.maxtry

• SQL Fiddle Demo

根据您的数据库，您也许可以利用分析函数，例如 ROW_NUMBER()，这将使这更容易。这是一个 another fiddle进行演示。

---

既然你使用的是postgresql，那么你应该可以使用解析ROW_NUMBER()函数。这也应该有效:

select *
from (
    select try, player_name, player_lastname, score, 
      Row_Number() Over (Partition By player_name, player_lastname order by try desc) rn
    from score
    ) s
where rn = 1

顺便说一句——我会考虑添加一个 player_id 作为主键。