mysql - SQL 查询中的 MAX() 和 `group by` 问题

Question

我正在写一个特别麻烦的查询。归结为:

我得到一个结构表:

pid | tid | points

在一个相当大的查询之后。

为了便于解释:

• pid = 问题编号
• tid = 团队编号
• points = 该团队因该问题获得的分数。

我想找到为特定 pid 获得最高分的团队。

我的问题有两个:

1. 如果这是一个名为 teampoints 的简单表，我如何获得每个 pid 都有 MAX(points) 的 tid？我尝试了 SELECT pid, tid, MAX(points) from teampoints group by pid; 但可以理解，这是行不通的

2. 我在相当大的查询之后得出了这个结果。如果我的第一个问题的答案涉及再次从 teampoints 中选择数据，有没有什么方法可以做到这一点而不必再次计算整个表？

谢谢

PS:我用的是mysql

---

血淋淋的细节:谨慎行事

我的系统中有几个表，它们的相关结构是:

users: uid
teams: tid | eid | teamname
teammembers: tid | uid
events: eid
problems: pid | eid
submissions: subid | pid | uid | eid | points | subts

一些注意事项: - 问题属于事件 - 用户属于团队 - 提交属于问题(pid)和用户(uid)。提交表有一个多余的 eid 字段，它总是可以从 pid 中确定。

用例是:

• 用户组成团队。用户由 uid 标识，团队由 tid 标识。团队成员存储在团队成员表中。
• 用户可以进行提交，这些提交存储在提交表中。投稿可获得积分。 subts 是提交时的 unix 时间戳。
• 用户可以针对同一个问题提交多次。最新提交(最大字幕)被计算在内。

现在，在此设置中，我想找到在任何给定事件 (eid) 中获得最高 points 的 teamname。

我希望这能说明我的情况。我只想问我需要知道的。我在评论中提供这些详细信息作为请求。

编辑:生成teampoints 表的查询是:

SELECT s.pid, teamlatest.tid, s.points 
  FROM  submissions s, teammembers tm, teams t, 
      (SELECT max(maxts) AS maxts, pid, tid 
         FROM (SELECT latest.maxts, latest.pid, t.tid 
                 FROM submissions s, teams t, teammembers tm,
                     (SELECT max(subts) AS maxts, pid, uid 
                        FROM submissions 
                        WHERE eid=3 AND status='P' 
                        GROUP BY pid, uid
                     ) AS latest
                 WHERE s.uid=latest.uid 
                   AND s.pid=latest.pid 
                   AND s.subts=latest.maxts 
                   AND latest.uid=tm.uid 
                   AND tm.tid=t.tid 
                   AND t.eid=3
              ) AS latestbyteam
         GROUP BY pid, tid) AS teamlatest
  WHERE s.pid=teamlatest.pid 
    AND teamlatest.tid=t.tid 
    AND t.tid=tm.tid 
    AND tm.uid=s.uid 
    AND s.subts=teamlatest.maxts

Answer 1

1. 一种方式:

   SELECT pid, tid, points 
     FROM teampoints
     WHERE (pid, points) IN (
         SELECT pid, MAX(points) 
           FROM teampoints GROUP BY pid
     )
   

   另一个，使用连接:

   SELECT s1.*
       FROM teampoints AS s1
       LEFT JOIN teampoints AS s2
          ON s1.pid = s2.pid
          AND s1.points < s2.points
       WHERE s2.tid IS NULL
   

2. 您可以 INSERT INTO复杂查询的临时表:

   CREATE TEMPORARY TABLE scores (
       pid INT, tid INT, points INT,
       KEY pp (pid, points)
   );
   INSERT INTO scores (pid, tid, points) 
       SELECT <a complex query>
   

   然后从中选择得分最高的球员。