如何在MySQL中用ORDER BY计算拥有相同姓名的学生的分数总和？

为此，请使用ORDER BY和GROUP BY子句。首先，让我们创建一个具有学生姓名和分数的表-

mysql> create table countRowValueDemo
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentName varchar(20),
   -> StudentMathScore int
   -> );
Query OK, 0 rows affected (0.71 sec)

以下是使用insert命令将记录插入表的查询-

mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('Larry',45);
Query OK, 1 row affected (0.19 sec)

mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('Mike',56);
Query OK, 1 row affected (0.16 sec)

mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('John',60);
Query OK, 1 row affected (0.15 sec)

mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('David',40);
Query OK, 1 row affected (0.24 sec)

mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('David',70);
Query OK, 1 row affected (0.12 sec)

mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('John',80);
Query OK, 1 row affected (0.13 sec)

mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('David',88);
Query OK, 1 row affected (0.17 sec)

以下是使用select语句从表中显示所有记录的查询-

mysql> select * from countRowValueDemo;

这将产生以下输出-

+-----------+-------------+------------------+
| StudentId | StudentName | StudentMathScore |
+-----------+-------------+------------------+
| 1         | Larry       | 45               |
| 2         | Mike        | 56               |
| 3         | John        | 60               |
| 4         | David       | 40               |
| 5         | David       | 70               |
| 6         | John        | 80               |
| 7         | David       | 88               |
+-----------+-------------+------------------+
7 rows in set (0.00 sec)

Case 1: 从大到小排序（总和）

以下是计算拥有相同姓名的学生的分数总和的查询。结果将按降序显示-

mysql> select StudentName,
   -> sum(StudentMathScore) AS TOTAL_SCORE
   -> from countRowValueDemo
   -> group by StudentName
   -> order by sum(StudentMathScore) desc;

这将产生以下输出-

+-------------+-------------+
| StudentName | TOTAL_SCORE |
+-------------+-------------+
| David       | 198         |
| John        | 140         |
| Mike        | 56          |
| Larry       | 45          |
+-------------+-------------+
4 rows in set (0.00 sec)

Case 2: 从小到大排序（总和）

以下是计算拥有相同姓名的学生的分数总和的查询。结果将按升序显示-

mysql> select StudentName,
   -> sum(StudentMathScore) AS TOTAL_SCORE
   -> from countRowValueDemo
   -> group by StudentName
   -> order by sum(StudentMathScore);

这将产生以下输出-

+-------------+-------------+
| StudentName | TOTAL_SCORE |
+-------------+-------------+
| Larry       | 45          |
| Mike        | 56          |
| John        | 140         |
| David       | 198         |
+-------------+-------------+
4 rows in set (0.00 sec)

阅读更多：MySQL 教程