通过两列具有同等重要性的列来命令MySQL表

I have a two player game which stores scores in a table.

我有一个双人游戏,在桌子上存储分数。

I want to select high scores from the table, this is my current code:

我想从表中选择高分,这是我目前的代码:

SELECT * FROM games ORDER BY GREATEST(player1Score,player2Score) DESC LIMIT 10

SELECT * FROM games ORDER BY GREATEST(player1Score,player2Score)DESC LIMIT 10

The problem is it only returns one instance of each row, even if for example the lower of the two scores in row 1 warrants inclusion in the top 10.

问题是它只返回每一行的一个实例,即使例如第1行中两个分数中的较低者保证包含在前10行中。

3 个解决方案

#1

Use UNION ALL:

使用UNION ALL:

SELECT col1, col2, col3, player1Score AS score FROM games
UNION ALL
SELECT col1, col2, col3, player2Score AS score FROM games
ORDER BY score DESC
LIMIT 10

Also, don't use SELECT *. List the columns explicitly.

另外,不要使用SELECT *。明确列出列。

#2

( SELECT *, player1Score as score
  FROM games 
  ORDER BY score DESC 
  LIMIT 10
)
UNION ALL
( SELECT *, player2Score AS score 
  FROM games 
  ORDER BY score DESC 
  LIMIT 10
)
ORDER BY score DESC
LIMIT 10

#3

You would probably be better off creating a players table and a join table (even though there are only two users). Then you could easily create a query to do what you're trying to do. Of course, it will take a little bit to alter your update/save functions to match the new schema.

你可能最好创建一个播放器表和一个连接表(即使只有两个用户)。然后,您可以轻松创建一个查询来执行您要执行的操作。当然,更改更新/保存功能以匹配新架构需要一点点。

players
-----
playerId
playerName

joinPlayerGame
-----
joinId
playerId
gameId

games
-----
modify the player score fields to just be 'score'

SELECT g.*, p.playerName FROM players p INNER JOIN joinPlayerGame j ON j.playerId = p.playerId INNER JOIN games g ON g.<whatever your key is> = j.gameId ORDER BY g.score DESC LIMIT 10

I hope that helps, good luck!

我希望有所帮助,祝你好运!

#1