时间戳

首页 > 解决方案 > groupby 的 SQL 返回最大值

sql - 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

标签： sqlpostgresql

解决方案

如果我理解正确，你想要这个：

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 中选择任何列，则根本不需要加入这些表

推荐阅读