mysql - 根据 MySQL SUM 结果选择最高行，有关系

Question

我有以下 MySQL 查询:

SELECT
s.student_id, s.student_firstname, s.student_lastname, s.isActive, 
    c.city_name,
    sd.student_startdate, sd.student_enddate,
    SUM(scpe.scpe_estemated_days) AS total
        FROM students s 
            INNER JOIN cityselections c ON c.city_id = s.student_city_id
            INNER JOIN studentdates sd ON sd.student_id = s.student_id
            LEFT JOIN studentcourseplan scp ON scp.student_id = s.student_id
            LEFT JOIN studentcourseplanelements scpe ON scpe.scpe_cpl_id = scp.cpl_id
                GROUP BY scp.cpl_id

这可以输出:

+------------+-------------------+------------------+----------+------------+-------------------+-----------------+-------+
| student_id | student_firstname | student_lastname | isActive | city_name  | student_startdate | student_enddate | total |
+------------+-------------------+------------------+----------+------------+-------------------+-----------------+-------+
| 83         | John              | Doe              | 1        | Dallas     | 2012-07-23        | 2012-09-30      | 413   |
| 84         | Derp              | Derpson          | 1        | Texas      | 2012-07-01        | 2012-08-26      | 413   |
| 85         | Barack            | Obama            | 1        | Washington | 2012-08-02        | 2012-08-31      | 2     |
| 85         | Barack            | Obama            | 1        | Washington | 2012-08-02        | 2012-08-31      | 153   |
+------------+-------------------+------------------+----------+------------+-------------------+-----------------+-------+

现在我只想为每个 student_id 打印 total 列中具有最高值的行

我尝试了 MySQL MAX() 但我无法让它工作。

应该怎么做？

Answer 1

尝试使用这个查询...

SELECT A.student_id, A.student_firstname, A.student_lastname, A.isActive, 
    A.city_name,
    A.student_startdate, A.student_enddate,
    MAX(A.total)
 FROM (SELECT
s.student_id, s.student_firstname, s.student_lastname, s.isActive, 
    c.city_name,
    sd.student_startdate, sd.student_enddate,
    SUM(scpe.scpe_estemated_days) AS total
        FROM students s 
            INNER JOIN cityselections c ON c.city_id = s.student_city_id
            INNER JOIN studentdates sd ON sd.student_id = s.student_id
            LEFT JOIN studentcourseplan scp ON scp.student_id = s.student_id
            LEFT JOIN studentcourseplanelements scpe ON scpe.scpe_cpl_id = scp.cpl_id
                GROUP BY scp.cpl_id) AS A
GROUP BY A.student_id;

希望对你有帮助....