从多个查询中提取一行最大数据

Question

我经营着一个篮球统计网站，我有一个名为game_stats_lakers的表。

我希望能够从某些列、得分、助攻、篮板(totr(中提取最大值，然后从任何数字中提取与该数字相关的比赛id(gid(。

我想把它按所有赛季(sid(分组，球员(playerid(在队里。

每个游戏只有一个gid，但显然每个stat max可能来自不同的游戏。简单来说，我希望每个球员每个赛季都能拿到最高分、助攻和篮板。

我希望查询结果如下所示。

playerid  sid    max_points  max_points_gid  max_assists  max_assists_gid  max_totr max_totr_gid
2         0304r  60          234             15           203              20       544
2         0405r  62          271             16           135              15       356        

这有道理吗？

我只是不知道该怎么做，我试过很多不同的查询，但都不正确。这些都是我实际的场上名字(得分、助攻、totr、gid、playerid、sid(，我只是以随机数为例。

非常感谢您的帮助！

我的game_stats_lakers表的格式如下所示。

gid teamid sid   playerid min fgm fga 3ptm 3pta ftm fta points assists totr 
233 2      0304r 300      35  3   10  2    5    10  12  18     13      6
234 2      0304r 300      26  2   5   0    1    2   3   6      2       3
235 2      0304r 300      29  10  20  3    8    10  12  33     4       2

使用GMB下面的第二个解决方案是接近的。我粘贴了我的代码和结果。问题是每一季的gid都没有变化，从第一季开始就一直保持不变。结果如下。想法？

查询运行：

select
playerid,
sid,
max(points) max_points,
(
select t1.gid 
from game_stats_lakers t1 
where t1.playerid = t.playerid 
order by t1.points desc limit 1
) max_points_gid,
max(assists) max_assists,
(
select t1.gid 
from game_stats_lakers t1 
where t1.playerid = t.playerid 
order by t1.assists desc limit 1
) max_assists_gid,
max(totr) max_totr,
(
select t1.gid 
from game_stats_lakers t1 
where t1.playerid = t.playerid 
order by t1.totr desc limit 1
) max_totr_gid from game_stats_lakers t group by playerid, sid

此处显示结果。。。注意重复的GID。

playerid sid    max_points  max_points_gid   max_assists  max_assists_gid max_totr  max_totr_gid
2        0304r  45          233              13           669             10        404 
2        0405r  42          233              14           669             8         404
2        0506r  81          233              11           669             12        404
2        0607r  60          233              16           669             11        404

Answer 1

伙计们，我终于做到了，我需要添加sid比较，这每年都会产生不同的gid。感谢GMB让我走上了正确的道路。这是最后一个有效的查询。

select
playerid,
sid,
max(points) max_points,
(
select t1.gid 
from game_stats_lakers t1 
where t1.playerid = t.playerid and t1.sid = t.sid
order by t1.points desc limit 1
) max_points_gid,
max(assists) max_assists,
(
select t1.gid 
from game_stats_lakers t1 
where t1.playerid = t.playerid and t1.sid = t.sid
order by t1.assists desc limit 1
) max_assists_gid,
max(totr) max_totr,
(
select t1.gid 
from game_stats_lakers t1 
where t1.playerid = t.playerid and t1.sid = t.sid 
order by t1.totr desc limit 1
) max_totr_gid from game_stats_lakers t group by playerid, sid

Answer 2

如果你运行的是MySQL 8.0，你可以用窗口函数来完成：

select distinct
player_id,
sid,
max(points) over(partition by player_id, sid) max_points
first_value(game_id) over(partition by player_id, sid order by score desc) max_points_gid,
max(assists) over(partition by player_id, sid) max_assists
first_value(game_id) over(partition by player_id, sid order by assists desc) max_assists_gid,
max(totr) over(partition by player_id, sid) max_totr
first_value(game_id) over(partition by player_id, sid order by totr desc) max_totr_gid
from game_stats_lakers

---

在早期版本中，几个相关的子查询可能是一种合理的方法：

select
player_id,
sid,
max(points) max_score,
(
select t1.game_id 
from game_stats_lakers t1 
where t1.player_id = t.player_id and t1.sid = t.sid
order by t1.points desc limit 1
) max_points_gid,
max(assists) max_assists,
(
select t1.game_id 
from game_stats_lakers t1 
where t1.player_id = t.player_id and t1.sid = t.sid 
order by t1.assists desc limit 1
) max_assists_gid,
max(totr) max_totr,
(
select t1.game_id 
from game_stats_lakers t1 
where t1.player_id = t.player_id and t1.sid = t.sid
order by t1.totr desc limit 1
) max_totr_gid
from game_stats_lakers t
group by player_id, sid