MySQL SELECT MAX的SUM()值

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL SELECT MAX的SUM()值，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含1658字，纯文字阅读大概需要3分钟。

内容图文

我有这个MySQL查询：

SELECT 
    SUM(scpe.scpe_estemated_days) AS total_days,
    scp.cpl_startdate
FROM
    studentcourseplanelements scpe
        INNER JOIN
    studentcourseplan scp ON scp.cpl_id = scpe.scpe_cpl_id
        INNER JOIN
    (SELECT 
        sd1.student_id, sd1.student_startdate
    FROM
        studentdates sd1
    WHERE
        sd1.student_id = '360'
    LIMIT 1) sd ON sd.student_id = scp.student_id
GROUP BY scp.cpl_id

这输出：

+------------+---------------+
| total_days | cpl_startdate |
+------------+---------------+
|          5 | 2012-11-01    |
|        129 | 2012-11-02    |
+------------+---------------+

我想在我的例子129中只选择具有最高total_days的行.

我怎样才能做到这一点？

解决方法:

这将显示具有最高total_days的重复记录,例如

+------------+---------------+
| total_days | cpl_startdate |
+------------+---------------+
|          5 | 2012-11-01    |
|        129 | 2012-11-02    |  <= shown
|        129 | 2012-11-03    |  <= shown
+------------+---------------+

询问

SELECT  SUM(scpe.scpe_estemated_days) AS total_days,
        scp.cpl_startdate
FROM    studentcourseplanelements scpe INNER JOIN studentcourseplan scp
            ON scp.cpl_id = scpe.scpe_cpl_id
        INNER JOIN
        (SELECT  sd1.student_id, sd1.student_startdate
         FROM    studentdates sd1
         WHERE   sd1.student_id = '360'
         LIMIT 1) sd ON sd.student_id = scp.student_id
GROUP BY scp.cpl_id
HAVING SUM(scpe.scpe_estemated_days) = 
    (
        SELECT MAX(total_days)
        FROM
        (
            SELECT  SUM(scpe.scpe_estemated_days) AS total_days,
            FROM    studentcourseplanelements scpe INNER JOIN studentcourseplan scp 
                         ON scp.cpl_id = scpe.scpe_cpl_id
                    INNER JOIN
                    (SELECT  sd1.student_id, sd1.student_startdate
                     FROM    studentdates sd1
                     WHERE   sd1.student_id = '360'
                     LIMIT 1) sd ON sd.student_id = scp.student_id
            GROUP BY scp.cpl_id
        ) s
    )

内容总结

以上是互联网集市为您收集整理的MySQL SELECT MAX的SUM()值全部内容，希望文章能够帮你解决MySQL SELECT MAX的SUM()值所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端