选择两列之和的最大值 [英] Selecting max of a sum of two columns

问题描述

我有一个表比较.如果我跑

I have a table comparisons. If I run

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 |
+--------------+----------+----------+------+------+

我想选择stu1Vers + stu2Vers最大的整行.我一直在尝试一些类似的方法

I'd like to select the entire row where stu1Vers+stu2Vers is the maximum. I keep trying something along the lines of

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;

但是返回的东西是相当随机的:

but that returns a rather random assortment of things:

+--------------+----------+----------+--------------+
| comparisonid | stu1vers | stu2vers | max(totvers) |
+--------------+----------+----------+--------------+
|          220 |        1 |        1 |           21 |
+--------------+----------+----------+--------------+

我正在尝试在第一个表中获取第392行.

I'm trying to get row 392 in the first table.

推荐答案

如果在多行具有相同最大值的情况下想要所有行，则可以使用以下查询:

If you want all the rows when there are multiple rows with the same maximum value, then you can use this query:

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.

Regarding your update to the question, I'm not sure what you mean to return all the rows grouped by stu1 and stu2. Perhaps you mean ordered by these columns? If so, add ORDER BY stu1, stu2 to the query.

这篇关于选择两列之和的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！