mysql 从每个游戏中获取最高玩家得分- SQL [duplicate]

g2ieeal7 于 2022-12-17 发布在 Mysql

关注(0)|答案(3)|浏览(212)

此问题在此处已有答案：

SQL select only rows with max value on a column [duplicate]（27个答案）
3天前关闭。
我需要找到每个游戏的最高分。沿着游戏名称，类别，玩家代码（球员的名字）谁得到了最高分，日期（时间）时，分数设置和ofc的分数本身。
我的表设置如下

CREATE TABLE Player(
PlayerId INT,
Date DATE,
PlayerCode CHAR(3),
PRIMARY KEY (PlayerId)
);

CREATE TABLE Game(
GameId INT,
Name VARCHAR(128),
Description VARCHAR(256),
Launched DATE,
Category ENUM('Adventure', 'Action', 'RPG', 'Simulation', 'Sports', 'Puzzle', 'Other'),
PRIMARY KEY (GameId)
);

CREATE TABLE Results(
PlayerId INT,
GameId INT,
Time DATETIME,
Score INT,
PRIMARY KEY (PlayerId,GameId,Time),
CONSTRAINT ResultsGameFK FOREIGN KEY (GameId) REFERENCES Game (GameId),
CONSTRAINT ResultsPlayerFK FOREIGN KEY (PlayerId) REFERENCES Player (PlayerId)
);

到目前为止，我只能得到每一场比赛中每个球员的每一个分数。当每场比赛的最高分数是我所关心的。我只是最近才了解到加入，但这一个让我困惑。谢谢

Select Game.Name, Game.Category, PlayerCode, Score, Time
from  Player
JOIN Results
ON Player.PlayerId = Results.PlayerId
JOIN Game
ON Game.GameId = Results.GameId 
group by Game.Name, Game.Category, Score, PlayerCode, Time
order by Score DESC

mysql

来源：https://stackoverflow.com/questions/74783395/get-highest-playerscore-from-each-game-sql

3条答案

按热度按时间

hujrc8aj1#

理想情况下，您可以使用RANK，但如果RANK不可用（例如，您的数据库版本较旧），则可以编写一个允许多个玩家平局的解决方案：

SELECT 
  Game.Name as GameName, 
  Game.Category AS GameCategory,
  Player.PlayerCode AS PlayerCode, 
  Results.R_Time AS HighscoreTime,
  Results.Score AS Highscore
FROM Results
JOIN (
  SELECT 
    GameId, 
    MAX(Score) AS max_score
  FROM Results
  GROUP BY GameId ) ms
ON Results.GameId = ms.GameId
AND Results.Score = ms.max_score
JOIN Game
ON Results.GameId = Game.GameId
JOIN Player
ON Results.PlayerId = Player.PlayerId;

如果您 * 可以 * 使用RANK，并且可以接受多个高分（例如平局），请尝试：

SELECT 
  Game.Name as GameName, 
  Game.Category AS GameCategory,
  Player.PlayerCode AS PlayerCode, 
  rr.R_Time AS HighscoreTime,
  rr.Score AS Highscore
FROM (
  SELECT 
    PlayerId, 
    GameId, 
    R_Time, 
    Score,
    RANK() OVER(PARTITION BY GameId ORDER BY Score DESC) AS rank_score
  FROM Results ) rr
JOIN Game
ON rr.GameId = Game.GameId
JOIN Player
ON rr.PlayerId = Player.PlayerId
WHERE rr.rank_score = 1;

如果每场比赛最多只能有一个结果，请将RANK更改为：

RANK() OVER(PARTITION BY GameId ORDER BY Score DESC, R_Time, PlayerID) AS rank_score

这里的逻辑是得分最高、最早的玩家得到最高分。（...如果这些是相同的，则通过玩家ID打破平局--这可能不是你想要的--根据需要进行调整）
注我更改了几个列名，以避免与SQL关键字（Time，Date）冲突。

赞(0）回复(0）举报 2022-12-17

wydwbb8l2#

给你

select * 
from game
right join (select c.GameId, Max(c.Score)
            from player 
            right join (select results.PlayerId, results.GameId, results.Time, results.Score, game.Name, game.Description, game.Launched, game.Category
                        from results 
                        left join game 
                        on results.GameId = game.GameId) AS c
            on player.PlayerId = c.PlayerId
            group by c.GameId
            order by Max(c.Score)) as d
on game.GameId = d.GameId

将为您的数据集中的每个游戏提供最高分：

编辑：

如果你不介意的纽带和许多子查询，那么这包括播放器：

select * 
from player
right join (select game.GameId, game.Name, game.Description, game.Launched, game.Category, e.playerId, e.Time, e.maxScore
            from game
            right join (select results.playerId, results.GameId, results.Time, d.maxScore
                        from results
                        right join (select c.GameId, Max(c.Score) as maxScore
                                    from player 
                                    right join (select results.PlayerId, results.GameId, results.Time, results.Score, game.Name, game.Description, game.Launched, game.Category
                                                from results 
                                                left join game 
                                                on results.GameId = game.GameId) AS c
                                    on player.PlayerId = c.PlayerId
                                    group by c.GameId
                                    order by Max(c.Score)) AS d
                        on results.GameId = d.GameId
                        where results.score = d.maxScore) AS e
            on game.GameId = e.GameId) AS f
on player.playerId = f.playerId

赞(0）回复(0）举报 2022-12-17

wfveoks03#

您几乎已经做到了这一点。您已经应用了GROUP BY子句，这意味着您可以执行聚合。
在select子句中执行以下操作

Select Game.Name, Game.Category, PlayerCode, MAX(Score), Time

通过MAX（Score），您可以获得最高分，并且您已经有ORDER BY score DESC，这意味着您将首先获得最高分。

赞(0）回复(0）举报 2022-12-17

我来回答

mysql 从每个游戏中获取最高玩家得分- SQL [duplicate]

3条答案

相关问题

热门标签

最新问答