sql - 选择两列的最大值(重新访问)

Question

我问过类似的问题before只是后来发现我认为的答案没有用，因为我没有问正确的问题(并且没有注意到回答者告诉我这个)。回顾一下，我有一张比较表。我正在尝试为每组两个学生选择具有最大版本的行。所以我一直在做什么:

SELECT subID1, subID2, stu1,stu2,comparisonID,MAX(stu1vers+stu2vers) AS maxvers 
FROM comparisons
WHERE assignmentID=9 AND stu1!=stu2 
GROUP BY stu1,stu2;
+--------+--------+------+------+--------------+---------+
| subID1 | subID2 | stu1 | stu2 | comparisonID | maxvers |
+--------+--------+------+------+--------------+---------+
|     15 |     11 |    1 |    6 |           64 |       6 |
|     11 |      3 |    6 |    1 |           55 |       5 |
+--------+--------+------+------+--------------+---------+

不会工作，因为我只需要 maxvers 为 6 的行。我上次得到的答案是:

SELECT subID1,subID2,stu1,stu2, comparisonID 
FROM comparisons WHERE stu1Vers + stu2Vers = (
    SELECT MAX(stu1Vers+stu2Vers) 
    FROM comparisons 
    WHERE stu1 != stu2 AND assignmentid=9
) AND stu1!=stu2 AND assignmentid=9 
GROUP BY stu1,stu2;

我真的认为这是可行的 - 只是发现它在表中查询该分配的最大版本，然后查找与该最大版本匹配的行。但这并不好，因为两个学生的版本号可能较低:

+--------+--------+------+------+--------------+---------+
| subID1 | subID2 | stu1 | stu2 | comparisonID | maxvers |
+--------+--------+------+------+--------------+---------+
|     44 |     23 |   37 |   36 |          153 |       2 |
|     44 |     36 |   37 |   39 |          156 |       3 |
|     44 |     34 |   37 |   40 |          154 |       3 |
|     36 |     23 |   39 |   36 |           95 |       3 |
|     36 |     34 |   39 |   40 |           96 |       4 |
...
+--------+--------+------+------+--------------+---------+

我需要选择所有这些记录，因为 stu1 和 stu2 的每个组合都是唯一的。我如何为 stu1、stu2 的每个组合设置 max(sub1vers+sub2vers) 的行(也就是说，就像上面的第一个表一样，我仍然只需要 comparisonID 64)。

Answer 1

编辑 特定于 MySQL 的查询会为非聚合列生成不正确的值。请改用可移植查询。

假设您在 MySQL 下运行(根据您的问题标签和原始 SQL 查询)，那么您可以发出以下语句:

SELECT subID1, subID2,stu1,stu2,comparisonID,MAX(stu1vers+stu2vers) AS maxvers
 FROM comparisons
 WHERE assignmentID=9 AND stu1!=stu2
 GROUP BY LEAST(stu1,stu2), GREATEST(stu1,stu2);

<罢工>

如果您需要更好的可移植性(也能够在例如 Postgres 上发出查询)，您需要稍微复杂的查询，这里使用单个 JOIN:

SELECT c1.subID1, c1.subID2,c1.stu1,c1.stu2,c1.comparisonID,c2.versmax
 FROM comparisons AS c1
 INNER JOIN (
  SELECT
    LEAST(stu1,stu2) AS stuA,
    GREATEST(stu1,stu2) AS stuB,
    MAX(stu1vers+stu2vers) AS versmax
   FROM comparisons
   WHERE assignmentID=9 AND stu1<>stu2
   GROUP BY stuA, stuB
 ) AS c2
 ON ((c1.stu1=c2.stuA AND c1.stu2=c2.stuB) OR
     (c1.stu2=c2.stuA AND c1.stu1=c2.stuB)
    ) AND c1.stu1vers+c1.stu2vers=c2.versmax
 WHERE c1.assignmentID=9 AND c1.stu1<>c1.stu2;

请注意，如果两种组合产生相同的 maxvers(除非您决定提供规则以区分两者)，例如:

+--------+--------+------+------+--------------+---------+ 
| subID1 | subID2 | stu1 | stu2 | comparisonID | maxvers | 
+--------+--------+------+------+--------------+---------+ 
|     15 |     11 |    1 |    6 |           64 |       6 | 
|     11 |      3 |    6 |    1 |           55 |       6 | 
+--------+--------+------+------+--------------+---------+