groupby的SQL回傳最大值

我有以下帶有 4 個表的資料庫：scores, players, games, levels.

CREATE TABLE games(
   gameid     INTEGER  NOT NULL 
  ,name   VARCHAR(6) NOT NULL
  ,active VARCHAR(8) NOT NULL,
  PRIMARY KEY (gameid)
);

INSERT INTO games(gameid,name,active) VALUES (1,'pang','yes');
INSERT INTO games(gameid,name,active) VALUES (2,'pong','yes');
INSERT INTO games(gameid,name,active) VALUES (3,'pung','yes');

CREATE TABLE levels(
   levelid     INTEGER  NOT NULL
  ,name   VARCHAR(6) NOT NULL
  ,active VARCHAR(8) NOT NULL,
    PRIMARY KEY (levelid)

);
INSERT INTO levels(levelid,name,active) VALUES (1,'pang','yes');
INSERT INTO levels(levelid,name,active) VALUES (2,'pong','yes');
INSERT INTO levels(levelid,name,active) VALUES (3,'pung','yes');

CREATE TABLE player(
   playerid     INTEGER  NOT NULL 
  ,name   VARCHAR(6) NOT NULL
  ,surname VARCHAR(8) NOT NULL,
    PRIMARY KEY (playerid)

);
INSERT INTO player(playerid,name,surname) VALUES (1,'pang','yes');
INSERT INTO player(playerid,name,surname) VALUES (2,'pong','yes');
INSERT INTO player(playerid,name,surname) VALUES (3,'pung','yes');



CREATE TABLE scores (
    gameid int NOT NULL,
    levelid int NOT NULL,
    playerid int NOT NULL,
    score int NOT NULL

);

INSERT INTO scores(gameID,levelID, playerID, score) VALUES (1,1,1,50);
INSERT INTO scores(gameID,levelID, playerID, score) VALUES (1,1,2,60);
INSERT INTO scores(gameID,levelID, playerID, score) VALUES (1,1,3,100);
INSERT INTO scores(gameID,levelID, playerID, score) VALUES (2,1,2,60);
INSERT INTO scores(gameID,levelID, playerID, score) VALUES (2,1,3,500);

我試圖用每個游戲的每個級別的主題分數回傳玩家。目前，我似乎無法弄清楚為什么我要重新分配每個玩家的最高分數。我的代碼如下：

SELECT g.gameid, l.levelid, p.playerid, ts.top_score
FROM scores s
INNER JOIN games g ON g.gameid = s.gameid
INNER JOIN levels l ON l.levelid = s.levelid
INNER JOIN player p ON p.playerid = s.playerid

INNER JOIN (
  SELECT gameID, levelid, MAX(score) AS top_score FROM scores GROUP BY gameid, levelid
  ) ts ON (s.gameid = ts.gameid AND s.levelid = ts.levelid)

GROUP BY g.gameid, l.levelid, p.playerid, ts.top_score

資料庫小提琴可用：https : //www.db-fiddle.com/f/peyKD96aPmZTjY1MSn1ik2/4

期望的輸出是：

gameid, levelid, playerid, score 
  1,       1,      3,       100
  2,       1,      3,       500

uj5u.com熱心網友回復：

如果我理解正確，你想要這個：

select g.gameid, l.levelid, p.playerid, s.score 
from (
  select * , row_number() over (partition by gameid,levelid order by score desc) rn 
  from scores
) s 
join games g on s.gameId = g.gameid
join levels l on l.levelid = s.levelid
join player p on p.playerid = s.playerid
where s.rn = 1;

但是，如果您沒有從 l、p 或 g 中選擇任何列，則您實際上根本不需要加入這些表

uj5u.com熱心網友回復：

您需要使用磁區方法來查找每個組中的頂部。這里有一個很好的解釋：http : //www.silota.com/docs/recipes/sql-top-n-group.html

uj5u.com熱心網友回復：

與@eshirvana 的解決方案相同，但它使用 Rank() - 如果多個玩家的最大分數相同，則列出所有玩家。

WITH RANKING(gameid,levelid,playerid,score,rn) AS
 (
   select gameid,levelid,playerid,score, 
          Rank() over (partition by gameid,levelid order by score desc) rn 
     from scores
 )
 select gameid,levelid,playerid,score from ranking where rn=1

標籤：sql PostgreSQL

上一篇：RANK()OVER是否可以給出'最受歡迎'的值

下一篇：從Postgres中的另一個表中更新具有最高值的列？