mysql:限制结果集中每个播放器返回的记录数

dxpyg8gm  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(388)

如何更改以下查询以限制结果,使每个人只显示两个游戏而不是四个?

这将使我更容易回答这个问题,这两个人最近都玩了哪两个游戏?或者类似的大集合问题,其中每个人返回许多记录。
我在mysql中通过phpmyadmin使用下面的sql来返回一个游戏列表,供一组回答问题的人使用,对于这组人,他们最近玩了哪些游戏?
目前每个人玩过的不同游戏都会出现在结果中。我想知道我是否可以限制每个人的游戏数量,例如两个。这样我就可以更容易地回答上面的问题了。
playgroup.fullname是一个包含我要为其创建列表的四个人的表。

查询

SELECT Games.Title, playgroup.FullName, Max(Responses.PlayDate)

FROM (Players INNER JOIN (Games INNER JOIN Responses ON Games.ID = Responses.Title) ON Players.ID = Responses.Player) INNER JOIN PlayGroup ON Players.ID = PlayGroup.FullName

GROUP BY Games.Title, PlayGroup.FullName

ORDER BY playgroup.FullName ASC, Max(Responses.PlayDate) DESC;

当前结果

对查询的什么更改会导致每个全名只返回两个游戏?

Title,
FullName,
Max(Responses.PlayDate)

Dutch Blitz
1
2020-06-14 00:00:00.000000

PitchCar
1
2020-06-13 00:00:00.000000

Bohnanza
1
2020-05-31 00:00:00.000000

Geocaching
1
2020-05-25 00:00:00.000000

Patchwork
2
2020-05-26 00:00:00.000000

Dominion
2
2020-05-25 00:00:00.000000

Geocaching
2
2020-05-25 00:00:00.000000

Disc Golf
2
2020-05-24 00:00:00.000000

Dutch Blitz
4
2020-06-14 00:00:00.000000

Bohnanza
4
2020-05-31 00:00:00.000000

Ingenious
4
2020-04-28 00:00:00.000000

Qwirkle
4
2020-04-08 00:00:00.000000

Colossal Arena
141
2020-06-19 00:00:00.000000

Roll for the Galaxy
141
2020-06-18 00:00:00.000000

Fortnite
141
2020-06-16 00:00:00.000000

Pendragon
141
2020-06-15 00:00:00.000000
6l7fqoea

6l7fqoea1#

可以使用窗口函数:

SELECT title, fullname, max_playdate
FROM (SELECT g.Title, pg.FullName, Max(r.PlayDate) as max_playdate,
            ROW_NUMBER() OVER (PARTITION BY g.Title ORDER BY MAX(r.PlayDate) DESC) as seqnum
      FROM Players p INNER JOIN
           Games g INNER JOIN
           Responses r
           ON r.ID = r.Title JOIN
           Players p
           ON p.ID = r.Player INNER JOIN
           PlayGroup pg
           ON p.ID = pg.FullName    
      GROUP BY g.Title, pg.FullName
     ) g
ORDER BY FullName ASC, max_PlayDate DESC;

相关问题