sql [db2] 带条件的选择语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14970631/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql [db2] select statement with condition
提问by user968420
I have two tables.
player1 has rows username, score, game_id, ...
player2 has rows username, score, game_id...
how do you select the username with the biggest score for the same game_id?
我有两张桌子。
player1 有用户名、分数、game_id
行、... player2 有用户名、分数、game_id 行...
如何为相同的game_id 选择得分最高的用户名？
what I have is
我拥有的是
SELECT player1.username winning,
player1.points,
player1.game_id
FROM player1
INNER JOIN player2 ON player1.game_id = player2.game_id
WHERE player1.points > player2.points
and
和
SELECT player2.username winning,
player2.points,
player2.game_id
FROM player1
INNER JOIN player2 ON player1.game_id = player2.game_id
WHERE player2.points > player1.points
回答by Manachi
This should do it:
这应该这样做：
SELECT
game_id,
CASE WHEN p1.score > p2.score THEN p1.username ELSE p2.username END AS winner
FROM
player1 p1
INNER JOIN
player2 p2 ON p1.game_id = p2.game_id
ORDER BY
p1.game_id ASC
As Darius mentioned, you may wish to adjust the CASE statement according to the situation of a Draw, you may change it to something like this:
正如 Darius 所说，你可能希望根据 Draw 的情况调整 CASE 语句，你可以将其更改为这样的：
CASE WHEN p1.score > p2.score THEN p1.username WHEN p1.score < p2.score THEN p2.username ELSE 'Draw' END AS winner
It really depends on what you want to display in that scenario.
这实际上取决于您要在该场景中显示的内容。
