sql - 如何获得第二佳值(value)

假设我们有两个表：

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

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

例如，如果

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

最佳答案

你可以试试这个吗

     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

您无法在此查询中按会话分组。因此，这就是为什么您需要将其放入子查询中并将其与gamescore结合以获取session_id的原因

关于sql - 如何获得第二佳值(value)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13494261/