代码之家 › 专栏 › 技术社区 › jrharshath

SQL查询中的MAX()和“group by”有问题

mysql

jrharshath · 技术社区 · 16 年前

我得到一个结构表:

pid | tid | points

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

为了便于解释:

pid
tid =团队id
points =对该问题给予该团队的分数。

我想找到一支在某个特定的pid中得分最高的球队。

我的问题有两个:

如果这是一个简单的表 teampoints ,如何获取 SELECT pid, tid, MAX(points) from teampoints group by pid; 但可以理解的是,这是行不通的
我经过一番大的询问后得出了这个结果。如果第一个问题的答案是从 团队积分 再说一次,有没有办法不用再计算整张表呢?

PS:我用mysql。

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

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

注意事项: -用户属于团队

用例是:

用户组成团队。用户由 uid ,团队 每日三次
用户可以进行提交,这些提交存储在提交表中。投稿将获得积分。subts是提交时的unix时间戳。

现在,在这种情况下,我想找到 teamname 得分最高的 点数

团队积分 表为:

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

3 回复 | 直到 16 年前

outis 16 年前

单向:

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

你可以 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>

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

Phil Wallach 16 年前

select pid, tid, points
from teampoints tp
where not exists (
    select 1
    from teampoints tp1
    where tp.pid = tp1.pid
    and tp.points < tp1.points)

或者类似的。。。。

Lieven Keersmaekers 16 年前

您可以将第一个查询的结果存储在临时表中,然后尝试以下操作

SELECT  pid, tid, points
FROM    teampoints tp
        INNER JOIN (
          SELECT    pid, points = MAX(points) 
          FROM      teampoints 
          GROUP BY  pid
        ) tp_max ON tp_max.pid = tp.pid AND tp_max.points = tp.points