sql - 如何获得次优值

Question

假设我们有两个表:

Players(player_id int)
GameScores(player_id int, session_id int, score int)

我们如何查询每个玩家的第二高分 session ？

例如如果

Players
1
2

GameScores
(player_id, session_id, score)
1 1 10
1 2 20 
1 3 40
2 4 10
2 5 20

Then result would be
(player_id, session_id)
1, 2
2, 4

Answer 1

你能试试这个吗

     SELECT GameScores.player_id, GameScores.session_id 
     FROM (
        SELECT player_id,MAX(score) as SecondScore
        FROM GameScores g
        WHERE score < (SELECT Max(Score) FROM gameScore where gameScore.player_id = g.player_id)
        GROUP BY player_id 
        ) x
        INNER JOIN GameScores ON x.player_id = gamescore.player_id 
          AND x.SecondScore = gamescore.score

---

这是为每个玩家选择第二高分的查询

SELECT player_id,MAX(score) as SecondScore
            FROM GameScores g
            WHERE score < (SELECT Max(Score) FROM gameScore where gameScore.player_id = g.player_id)
            GROUP BY player_id

您不能在此查询中按 session 分组。所以这就是为什么您需要将其放入子查询并将其加入 gamescore 以获取 session_id