mysql - 有两个条件得到第二高的值

Question

这是我的 table :

+--------+-------+--------+--------+
| player | points| score1 | score2 |
+--------+-------+--------+--------+
|      1 |   12  |   9    |    1   |
|      2 |   12  |   14   |    2   |
|      3 |   10  |   12   |    3   |
|      4 |   9   |   10   |    4   |
|      5 |   8   |   10   |    4   |
+--------+-------+--------+--------+

为了获得第二高的值，我使用了这个查询:

SELECT player,points FROM players
WHERE points= (SELECT DISTINCT(points) FROM players as p1
WHERE (SELECT COUNT(DISTINCT(points))=2 FROM players as p2
WHERE p1.points<= p2.points)) ORDER BY player

它工作完美。但现在我想得到的是两个条件:

条件1:积分DESC

条件2:(score1-score2) DESC

此查询可以很好地获取具有第二高值的玩家(检查点数和 score1):

SELECT player,points FROM players

WHERE points= (SELECT DISTINCT(points) FROM players as p1
WHERE (SELECT COUNT(DISTINCT(points))=2 FROM players as p2
WHERE p1.points<= p2.points))

AND

WHERE score1= (SELECT DISTINCT(score1) FROM players as p1
WHERE (SELECT COUNT(DISTINCT(score1))=2 FROM players as p2
WHERE p1.score1<= p2.score1))

ORDER BY player

如何使用两个条件(分数 DESC 和 (score1-score2) DESC)获得第二高的玩家？

Answer 1

此查询将执行您想要的操作。它JOIN s players对自己，寻找第二个表中排名高于第一个表中玩家的所有玩家。在这种情况下，排名首先基于 points , 那么在 score1 之间的差分相等的情况下和 score2 .排名第二的玩家是指有一名玩家比自己排名更高的玩家:

SELECT p1.* 
FROM players p1
JOIN players p2 ON p2.points > p1.points
                OR p2.points = p1.points AND (p2.score1 - p2.score2) > (p1.score1 - p1.score2)
GROUP BY p1.player
HAVING COUNT(p2.player) = 1

输出:

player  points  score1  score2
1       12      9       1

您也可以使用 p2.player < p1.player 以类似的方式编写原始查询作为将得分相同的玩家分开的条件(模拟您的 ORDER BY player 的效果):

SELECT p1.* 
FROM players p1
JOIN players p2 ON p2.points > p1.points
                OR p2.points = p1.points AND p2.player < p1.player
GROUP BY p1.player
HAVING COUNT(p2.player) = 1

输出:

player  points  score1  score2
2       12      14      2

Demo on dbfiddle