我有一张桌子比较。如果我跑
SELECT comparisonID,stu1Vers,stu2Vers,stu1,stu2
from comparisons
WHERE stu1!=stu2 and assignmentid=9;
我得到的信息是:
+--------------+----------+----------+------+------+
| comparisonID | stu1Vers | stu2Vers | stu1 | stu2 |
+--------------+----------+----------+------+------+
| 287 | 12 | 2 | 1 | 6 |
| 286 | 12 | 1 | 1 | 6 |
| 276 | 11 | 2 | 1 | 6 |
| 275 | 11 | 1 | 1 | 6 |
| 266 | 10 | 2 | 1 | 6 |
| 265 | 10 | 1 | 1 | 6 |
| 257 | 9 | 2 | 1 | 6 |
| 256 | 9 | 1 | 1 | 6 |
...
| 391 | 19 | 1 | 1 | 6 |
| 392 | 19 | 2 | 1 | 6 |
+--------------+----------+----------+------+------+
我想选择整个Stu1FiS+Stu2VS最大的行。我一直在尝试
select c.comparisonid,c.stu1vers,c.stu2vers,max(totvers)
from comparisons as c join
(select comparisonid, stu1vers+stu2vers as totvers
from comparisons where stu1!=stu2 group by comparisonid) as cm
on c.comparisonid = cm.comparisonid and c.stu1vers+c.stu2vers = cm.totvers;
但这返回了一个相当随机的分类:
+--------------+----------+----------+--------------+
| comparisonid | stu1vers | stu2vers | max(totvers) |
+--------------+----------+----------+--------------+
| 220 | 1 | 1 | 21 |
+--------------+----------+----------+--------------+
我想把第392行放在第一张桌子上。
最佳答案
如果希望在具有相同最大值的多行时拥有所有行,则可以使用此查询:
SELECT * FROM Table1
WHERE stu1Vers + stu2Vers = (SELECT MAX(stu1Vers + stu2Vers) FROM Table1)
包括你的情况:
SELECT * FROM Table1
WHERE stu1Vers + stu2Vers = (
SELECT MAX(stu1Vers + stu2Vers)
FROM Table1
WHERE stu1!=stu2 and assignmentid=9
) AND stu1!=stu2 and assignmentid=9
结果:
392, 19, 2, 1, 6
关于您对这个问题的更新,我不确定返回按stu1和stu2分组的所有行是什么意思。也许你的意思是按这些列排序?如果是,则将
ORDER BY stu1, stu2
添加到查询中。