MySQL 计算每科成绩和并以降序排列 作者: Chuwen 时间: 2020-05-08 分类: MySQL # 问题 > 查询各个学生总成绩，并按总分从高到底排序 也就是要将 `Chinese`、`Math`、`...`、`Biology` 字段都加起来然后排列 # 有以下表 ``` +----+--------+-----+------------------+---------+---------+------+---------+-----------+---------+ | ID | Name | Age | Job | Chinese | English | Math | Physics | Chemistry | Biology | +----+--------+-----+------------------+---------+---------+------+---------+-----------+---------+ | 1 | 王建军 | 30 | Java讲师 | 100 | 98 | 99 | 96 | 97 | 100 | | 2 | 常庆林 | 28 | Linux讲师 | 100 | 100 | 98 | 93 | 99 | 96 | | 3 | 徐培成 | 35 | BigData讲师 | 100 | 100 | 100 | 98 | 96 | 100 | | 4 | 李永强 | 30 | javaEE开发工程师 | 100 | 93 | 91 | 74 | 89 | 100 | | 5 | 赵子昂 | 24 | python开发工程师 | 98 | 93 | 91 | 74 | 89 | 100 | | 6 | 桂阳 | 25 | C++开发工程师 | 100 | 98 | 93 | 91 | 99 | 82 | | 7 | 肖云龙 | 24 | Golang开发工程师 | 93 | 97 | 85 | 100 | 93 | 69 | | 8 | 李洋 | 23 | C#开发工程师 | 100 | 98 | 99 | 96 | 97 | 100 | | 9 | 卜孟龙 | 30 | BigData开发 | 98 | 93 | 100 | 100 | 73 | 92 | | 10 | 罗大鹏 | 22 | Java开发工程师 | 100 | 84 | 91 | 87 | 100 | 93 | | 11 | 尹正杰 | 18 | 高级运维工程师 | 100 | 100 | 100 | 100 | 100 | 100 | +----+--------+-----+------------------+---------+---------+------+---------+-----------+---------+ ``` # 解决思路 我当时就想着用 ``` SELECT SUM(`Chinese`+`Math`+`English`+`Physics`+`Chemistry`+`Biology`) AS 'total' FROM `classmate` ORDER BY `total` DESC; ``` 结果运行结果是： > 这显然不是我们要的结果 ``` mysql> SELECT SUM(`Chinese`+`Math`+`English`+`Physics`+`Chemistry`+`Biology`) AS 'total' FROM `classmate` ORDER BY `total` DESC; +-------+ | total | +-------+ | 6263 | +-------+ 1 row in set (0.02 sec) ``` --- 然后我就 Google 搜啊搜，搜了很久很久(真的好久...)。终于找到了一个合适的结果，原文链接：https://segmentfault.com/q/1010000002894877 ### 得知应该是这样的： ``` SELECT *,( Chinese + Math + English + Physics + Chemistry + Biology ) AS total FROM classmate ORDER BY `total` DESC ``` ### 运行结果完美，是我想要的结果 ``` mysql> SELECT *,( Chinese + Math + English + Physics + Chemistry + Biology ) AS total FROM classmate ORDER BY `total` DESC; +----+--------+-----+------------------+---------+---------+------+---------+-----------+---------+-------+ | ID | Name | Age | Job | Chinese | English | Math | Physics | Chemistry | Biology | total | +----+--------+-----+------------------+---------+---------+------+---------+-----------+---------+-------+ | 11 | 尹正杰 | 18 | 高级运维工程师 | 100 | 100 | 100 | 100 | 100 | 100 | 600 | | 3 | 徐培成 | 35 | BigData讲师 | 100 | 100 | 100 | 98 | 96 | 100 | 594 | | 8 | 李洋 | 23 | C#开发工程师 | 100 | 98 | 99 | 96 | 97 | 100 | 590 | | 1 | 王建军 | 30 | Java讲师 | 100 | 98 | 99 | 96 | 97 | 100 | 590 | | 2 | 常庆林 | 28 | Linux讲师 | 100 | 100 | 98 | 93 | 99 | 96 | 586 | | 6 | 桂阳 | 25 | C++开发工程师 | 100 | 98 | 93 | 91 | 99 | 82 | 563 | | 9 | 卜孟龙 | 30 | BigData开发 | 98 | 93 | 100 | 100 | 73 | 92 | 556 | | 10 | 罗大鹏 | 22 | Java开发工程师 | 100 | 84 | 91 | 87 | 100 | 93 | 555 | | 4 | 李永强 | 30 | javaEE开发工程师 | 100 | 93 | 91 | 74 | 89 | 100 | 547 | | 5 | 赵子昂 | 24 | python开发工程师 | 98 | 93 | 91 | 74 | 89 | 100 | 545 | | 7 | 肖云龙 | 24 | Golang开发工程师 | 93 | 97 | 85 | 100 | 93 | 69 | 537 | +----+--------+-----+------------------+---------+---------+------+---------+-----------+---------+-------+ 11 rows in set (0.05 sec) ``` 标签: MySQL