MySQL 计算每科成绩和并以降序排列

问题

查询各个学生总成绩,并按总分从高到底排序

也就是要将 ChineseMath...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)

如无特别声明,该文章均为 现在网NowTime.cc)原创,转载请遵循 署名-非商业性使用 4.0 国际(CC BY-NC 4.0) 协议,即转载请注明文章来源。


标签: MySQL

赞 (0)

添加新评论